sql-server-2008 - Sql 服务器 : How do I unpivot with an alias?

Question

我知道您可以使用 pivot 在列上使用别名，但我想使用别名 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

但是，当我执行此操作时出现错误。

我能够实现最终目标的唯一方法是使用 case select 中的声明条款，这不漂亮。

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

有没有更好的办法？

Answer 1

另一种方法是在 unpivot 之前使用别名，如下所示:

;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