PIVOT question

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

If you post consumable data, DDL and inserts or the like you will likely get a response.

1 Like