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?

Answer 1

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