# How to Pivot Multiple Columns

I have the following data,

``````Year        DRC             DISP        Id
0           140.21          0.00        5808
1           112.37          1.00        5808
0           140.21          0.00        5824
1           112.37          0.00        5824
``````

I want to pivot it like this,

``````Id          DRC Year 0      DISP Year 0     DRC Year 1      DISP Year 1
5808        140.21          0.00            112.37          1.00
5824        140.21          0.00            112.37          0.00
``````

I have tried this,

``````SELECT *
FROM
(
SELECT Year, DRC, DISP, ID From OriginalDataTable
) AS SourceTable

PIVOT
(
max(DRC)
for Year IN (, )
) AS PivotTable;
``````

It pivots on the DRC, but not on DISP as well. The result is like this (0, and 1 is the DRC value). I understand why that is, but how do I pivot on the DISP as well?

``````DISP        Id          0           1
0.0         5808        140.21      112.37
0.0         5824        140.21      112.37
``````

## 1 Answer

Instead of the `PIVOT` you can simply use the `CASE WHEN ...`. By using `GROUP BY` then use `MAX(CASE` will return your expected result:

Sample execution with the given sample data:

``````DECLARE @OriginalDataTable TABLE (
[Year] INT, DRC DECIMAL(18,2), DISP DECIMAL(18,2), Id INT);

INSERT INTO @OriginalDataTable ([Year], DRC, DISP, Id) VALUES
(0, 140.21, 0.00, 5808),
(1, 112.37, 1.00, 5808),
(0, 140.21, 0.00, 5824),
(1, 112.37, 0.00, 5824);

SELECT [Id],
MAX(CASE WHEN [Year] = 0 THEN DRC END)  AS [DRC Year 0],
MAX(CASE WHEN [Year] = 0 THEN DISP END) AS [DISP Year 0],
MAX(CASE WHEN [Year] = 1 THEN DRC END)  AS [DRC Year 1],
MAX(CASE WHEN [Year] = 1 THEN DISP END) AS [DISP Year 1]
FROM @OriginalDataTable
GROUP BY [Id]
``````

Output:

``````Id      DRC Year 0   DISP Year 0   DRC Year 1   DISP Year 1
5808    140.21       0.00          112.37       1.00
5824    140.21       0.00          112.37       0.00
``````

More explanation:

``````SELECT [Id],
CASE WHEN [Year] = 0 THEN DRC END   AS [DRC Year 0],
CASE WHEN [Year] = 0 THEN DISP END  AS [DISP Year 0],
CASE WHEN [Year] = 1 THEN DRC END   AS [DRC Year 1],
CASE WHEN [Year] = 1 THEN DISP END  AS [DISP Year 1]
FROM @OriginalDataTable
``````

The above query returns the result below:

``````Id      DRC Year 0  DISP Year 0  DRC Year 1  DISP Year 1
5808    140.21      0.00         NULL        NULL
5808    NULL        NULL         112.37      1.00
5824    140.21      0.00         NULL        NULL
5824    NULL        NULL         112.37      0.00
``````

To avoid the `NULL` values and the `Id` wise result, I used `GROUP BY Id` then `MAX()` of the each column, so in the final result we can get the not `NULL` values as result.

• So you are using MAX just because you need some kind of aggregate function? There is only 1 value per year. That's a great answer thanks. – peter Aug 10 '16 at 21:10
• @peter: I added some explanation for why need of the `MAX()` here. – Arulkumar Aug 11 '16 at 6:09