0

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!

Peter
  • 378
  • 2
  • 7
  • 20
  • I can get this result by adding this SQL Server Report Builder as a Column Group, but I need a query to do this instead of using Report Builder this time... – Peter May 18 '16 at 04:06

2 Answers2

2

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
Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
2

use PIVOT

select  *
from    (
            select  Item, Columns, Result
            from    thisTable 
        ) t
pivot   (
            max (Result)
            for Columns in (X, Y, Z)
        ) p
Squirrel
  • 23,507
  • 4
  • 34
  • 32