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(,0) 1VAL, ISNULL(,0) 2VAL, ISNULL(,0) 3VAL FROM (SELECT ID , type , subtype , VALUE , CUR FROM IDVals ) AS SourceTable PIVOT ( MAX(VALUE) FOR subtype IN (, , ) ) AS PivotTable WHERE type = 1