Sql Server:我如何使用别名unpivot?
问题描述:
我知道你可以在pivot
的列上使用别名,但我也想用别名unpivot
。Sql Server:我如何使用别名unpivot?
select UserId
, ContactMethod
, ContactMethodValue
from Users
unpivot (
ContactMethodValue for ContactMethod in
( HomePhone as [3000]
, OfficePhone as [3001]
, CellPhone as [3002]
, Fax as [3003]
, Website as [3005]
)
) as unpvt
但是,当我这样做时出现错误。
我一直能够实现我的最终目标的唯一方法是在select
子句中使用case
语句,这并不美观。
select UserId
, (case ContactMethod
when 'HomePhone' then 3000
when 'OfficePhone' then 3001
when 'CellPhone' then 3002
when 'Fax' then 3003
when 'Website' then 3005
end) as ContactMethod
, ContactMethodValue
from Users
unpivot (
ContactMethodValue for ContactMethod in
( HomePhone
, OfficePhone
, CellPhone
, Fax
, Website
)
) as unpvt
有没有更好的方法?
答
您不能在UNPIVOT函数内部分配别名,因此您必须使用CASE
表达式。
另一种方法是使用一个UNION ALL
并直接将新值立刻道:
select userid, 3000 as ContactMethod, homePhone as ContactMethodValue
from users
union all
select userid, 3001 as ContactMethod, OfficePhone as ContactMethodValue
from users
union all
select userid, 3002 as ContactMethod, CellPhone as ContactMethodValue
from users
union all
select userid, 3003 as ContactMethod, Website as ContactMethodValue
from users
union all
select userid, 3005 as ContactMethod, homePhone as ContactMethodValue
from users
+0
upvoted一个接受。这是我最终采取的路线。 – 2013-05-10 15:12:01
答
另一种方法是别名前的逆转置,如:
;with aliasedUsers as (
select
UserId,
HomePhone as [3000]
OfficePhone as [3001]
CellPhone as [3002]
Fax as [3003]
Website as [3005]
)
select UserId
, ContactMethod
, ContactMethodValue
from aliasedUsers
unpivot (
ContactMethodValue for ContactMethod in
( [3000]
, [3001]
, [3002]
, [3003]
, [3005]
)
) as unpvt
CASE语句是你如何做到的。您不能在UNPIVOT函数中使用别名。 – Taryn 2013-05-09 15:05:17
@bluefeet:该死的速度很快。我以前很怕那个。 – 2013-05-09 15:07:04