Conditional PIVOT/transform problem
- by IanC
Hi folks
I have a table with three columns, which we'll call ID1, ID2, and Value.
Sample data:
ID ID1 Value
1 1 0
1 2 1
1 3 1
1 3 2
1 4 0
1 4 1
1 5 0
1 5 2
2 1 2
Value is limited to 0, 1, or 2.
What I need to do is pivot/transform this data into a column-based count of how many times each possible Value appears, grouped by ID, ID1. The output of the above should be:
ID ID1 Val0 Val1 Val2
1 1 1 0 0
1 2 0 2 0
1 3 0 1 1
1 4 1 1 0
1 5 1 0 1
2 1 0 0 1
I'm using SQL Server 2008. How do I do this?