Adding aliasing to field names in Pivot SQL query

Question

I have a query below, and need to have field [Cmp-Goal-RF-148] (which is pivoted to be a column) - I need the column title to be something besides [Cmp-Goal-RF-148], so I suppose I need to alias it. Doing this throws up an error: ([Cmp-Goal-RF-148] AS 'Ghost'). What am I missing?

select * 
from 
(
   select EmpRvwPdDtl.Emp, EmpRvwPdDtl.Rvwr, 
     EmpRvwPdDtl.RvwItm, 
     CAST(EmpRvwPdDtl.RvwItmCom as VARCHAR(MAX)) as comment 
   from EmpRvwPdDtl
   inner join EmpRvwPd 
     on (EmpRvwPd.Emp=EmpRvwPdDtl.Emp)
   where EmpRvwPdDtl.RvwItmCom  is not null
     AND EmpRvwPd.Sup='RM04'
) as s
PIVOT 
(
   MAX(comment) for RvwItm in ([Cmp-Goal-RF-148])
) as pvit

Answer 1

You will add the alias in the final SELECT list:

select Emp, Rvwr,
    [Cmp-Goal-RF-148] as Ghost  -- alias goes here
from 
(
   select EmpRvwPdDtl.Emp, EmpRvwPdDtl.Rvwr, 
     EmpRvwPdDtl.RvwItm, 
     CAST(EmpRvwPdDtl.RvwItmCom as VARCHAR(MAX)) as comment 
   from EmpRvwPdDtl
   inner join EmpRvwPd 
     on (EmpRvwPd.Emp=EmpRvwPdDtl.Emp)
   where EmpRvwPdDtl.RvwItmCom  is not null
     AND EmpRvwPd.Sup='RM04'
) as s
PIVOT 
(
   MAX(comment) for RvwItm in ([Cmp-Goal-RF-148])
) as pvit