How to Pivot Multiple Columns

Question

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 ([0], [1])
) 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

Answer 1

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.