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