Change column name while using PIVOT SQL Server 2008

Question

    SELECT * FROM EmployeeAttributes
PIVOT (
     MAX(VALUE)
     FOR AttributeID IN ([DD14C4C2-FC9E-4A2E-9B96-C6A20A169B2E],[BE8149E2-0806-4D59-8482-58223C2F1735],[23B2C459-3D30-41CA-92AE-7F581F2535D4])
      ) P

Result

    EmployeeID                           DD14C4C2-FC9E-4A2E-9B96-C6A20A169B2E               BE8149E2-0806-4D59-8482-58223C2F1735               23B2C459-3D30-41CA-92AE-7F581F2535D4
------------------------------------ -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
329999EA-6288-4E7D-87E8-12FF865AB301 Rauf                                               23                                                 10
34E2B762-F065-42BB-B4D8-2252102F1C20 Amal                                               NULL 

                                          5

Now HOW can I change the column name to Name, Age, Salary respectively after the EmployeeID ?

score 21 · Accepted Answer · answered Nov 14 '11 at 14:28

21

Use AS to give a column alias.

SELECT EmployeeID,
       [DD14C4C2-FC9E-4A2E-9B96-C6A20A169B2E] AS Name,
       [BE8149E2-0806-4D59-8482-58223C2F1735] AS Age,
       [23B2C459-3D30-41CA-92AE-7F581F2535D4] AS Salary
FROM   EmployeeAttributes PIVOT ( MAX(VALUE) FOR AttributeID IN (
       [DD14C4C2-FC9E-4A2E-9B96-C6A20A169B2E],
       [BE8149E2-0806-4D59-8482-58223C2F1735],
       [23B2C459-3D30-41CA-92AE-7F581F2535D4]) ) P

answered Nov 14 '11 at 14:28

Martin Smith

438,706
87
741
845

+1 I knew easier solutions were bound to exist just not that it would be *that* easy . – Lieven Keersmaekers Nov 14 '11 at 14:32
1

@Martin Smith Can I use it with dynamic pivot too. I am going to use dynamic pivot. :) http://www.sqlprof.com/blogs/sqldev/archive/2008/04/12/pivots-with-dynamic-columns-in-sql-server-2005-2008.aspx – Rauf Nov 14 '11 at 14:44
@Rauf - Yes. You would just need to make sure that the dynamic SQL string generated was in this form. – Martin Smith Nov 14 '11 at 14:46

score 13 · Answer 2 · answered Nov 14 '11 at 14:29

Perhaps easier solutions exist but placing the PIVOT statement result into a subquery allows you to alias the columns in the outer select.

SELECT  EmployeeID = EmployeeID
        , Name = [DD14C4C2-FC9E-4A2E-9B96-C6A20A169B2E]
        , Age = [BE8149E2-0806-4D59-8482-58223C2F1735]
        , Salary = [23B2C459-3D30-41CA-92AE-7F581F2535D4]
FROM    (
          SELECT  * 
          FROM    EmployeeAttributes
          PIVOT   (MAX(VALUE) FOR AttributeID IN (
                  [DD14C4C2-FC9E-4A2E-9B96-C6A20A169B2E]
                  , [BE8149E2-0806-4D59-8482-58223C2F1735]
                  , [23B2C459-3D30-41CA-92AE-7F581F2535D4])
          ) P
        ) P

Change column name while using PIVOT SQL Server 2008

2 Answers2

Linked