I am learning about pivots in SQL Server. Here is my data (before)
I want to pivot the data so it comes out like the after shot:
Where the first number in the value column header corresponds to the type and the last number corresponds to the subtype.
I have made a start with the below query but this is limited to type = 1. I could do a separate pivot for each type and join them all together but I thought someone clever would have a better solution.
SELECT ID, type, ISNULL([1],0) 1VAL, ISNULL([2],0) 2VAL, ISNULL([3],0) 3VAL
FROM
(SELECT
ID
, type
, subtype
, VALUE
, CUR
FROM
IDVals
) AS SourceTable
PIVOT
(
MAX(VALUE)
FOR subtype IN ([1], [2], [3])
) AS PivotTable
WHERE
type = 1