sql server - SQL Transform Crosstab Pivot Data

Question

I am using SQL Server 2008 and would like to transform my data such that:

Dataset:

ID   Item  Columns  Result
1     1      X       A
2     1      Y       B
3     1      Z       C
4     2      X       D
5     2      Y       E
6     2      Z      NULL
7     3      X       F
8     3      Y       G
9     3      Z       H

Results Desired:

Item   X   Y   Z
 1     A   B   C
 2     D   E  NULL
 3     F   G   H

At this time, I am doing the following, then pasting the columns I need into Excel:

Select * from thisTable where Column=X
Select * from thisTable where Column=Y
Select * from thisTable where Column=Z

However, not all of the rows match up to can can't just smack the tables side by side. For columns without a Result, I'd like NULL to show up to fill in the rows to make them all the same number of records.

I looked up PIVOT but I don't think this works here...what is this type of data transformation called? I don't think it's a crosstab...

Thanks!

Answer 1

You can do a crosstab using conditional aggregation:

SELECT
    Item,
    [X] = MAX(CASE WHEN [Columns] = 'X' THEN Result END),
    [Y] = MAX(CASE WHEN [Columns] = 'Y' THEN Result END),
    [Z] = MAX(CASE WHEN [Columns] = 'Z' THEN Result END)
FROM thisTable
GROUP BY Item