SQL Pivot for multiple columns

Question

I have the below table 'EmpTemp':

Name | Category | Value1 | Value 2
John | Cat1     |  11    |   33
John | Cat2     |  22    |   44

I would like to have the below output for the table:

Name | Cat1_Value1 | Cat2_Value1 | Cat1_Value2 | Cat2_Value2
John |      11     |      11     |      33     |     44

I guess this give a basic idea of what kind of transformation i'm expecting. I have tried the below query that gives me a partial solution:

SELECT 
    Name, 
    Cat1 AS 'Cat1_Value1', 
    Cat2 AS 'Cat2_Value1', 
FROM EmpTemp AS T
PIVOT
(
    MAX(Value1)
    FOR Category IN (Cat1, Cat2)
) AS PVT

The above query gives me the following result:

Name | Cat1_Value1 | Cat2_Value1
John |      11     |      11

I am stuck how can I extend this pivot query. Any help is appreciated in advance.

score 1 · Answer 1 · answered Dec 11 '15 at 17:43

Here is a way to do it using Pivot.

First you need to unpivot the data then do the pivot

;with cte as
(
select name,val,col_name from yourtable 
cross apply (values 
            (value1,Category+'value1'),
            (value2,Category+'value2')
            ) cs(val,col_name)
  )
  select * from cte
  pivot(max(val) for col_name in([Cat1value1],
                                 [Cat1value2],
                                 [Cat2value1],
                                 [Cat2value2]
                                )) p

score 0 · Answer 2 · answered Dec 11 '15 at 17:42

Here is a simple way of doing it without using the pivot syntax:

select name,
       max(case when category = 'Cat1' then value1 end) as cat1_value1,
       max(case when category = 'Cat2' then value1 end) as cat2_value1,
       max(case when category = 'Cat1' then value2 end) as cat1_value2,
       max(case when category = 'Cat2' then value2 end) as cat2_value2
  from EmpTemp
 group by name

SQL Pivot for multiple columns

2 Answers2