Sql Server: How do I unpivot with an alias?

Question

I know that you can use an alias on a column with pivot, but I'd like to use an alias with unpivot as well.

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

However I get an error when I do this.

The only way I've been able to accomplish my end goal is to use a case statement in the select clause, which isn't pretty.

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

Is there a better way?

The CASE statement is how you do it. You cannot alias inside of the UNPIVOT function. — Taryn, May 09 '13 at 15:05
Note that Oracle supports the "as" syntax, so this is not a far-fetched need. — Necreaux, Jun 27 '18 at 17:20

score 4 · Answer 1 · answered May 29 '16 at 23:11

Another method is to alias before the unpivot, as with:

;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

This method preserves the efficiency of the unpivot (compared with the accepted answer), while getting the desired outcome. — Stobor, Sep 22 '20 at 01:45

score 2 · Accepted Answer · answered May 09 '13 at 15:14

You cannot assign an alias inside of the UNPIVOT function so you would have to use the CASE expression.

Another way would be to use a UNION ALL and just place the new values immediately:

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

The problem with this solution is it potentially requires multiple table scans. As you start to add more columns, filtering, etc. performance degrades. Unpivoting is likely to perform better on non-trivial examples. — Necreaux, Jun 27 '18 at 17:28

score 0 · Answer 3 · answered Jun 18 '21 at 22:18

You can wrap the aliases in another select statement so the aliases are recognized, and unpivot it from there:

select * from
        (select UserId,
            HomePhone      as [3000]
            OfficePhone    as [3001]
            CellPhone      as [3002]
            Fax            as [3003]
            Website        as [3005]
        from aliasedUsers) P
        unpivot (ContactMethodValue for ContactMethod in ([3000],[3001],[3002],[3003],[3005])) as unpvt

Sql Server: How do I unpivot with an alias?

3 Answers3