SQL 转换交叉表透视数据

问题描述：

我正在使用 SQL Server 2008 并希望转换我的数据，以便:

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

数据集:

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

预期结果:

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

这时候，我在做下面的事情，然后把我需要的列粘贴到Excel中:

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

然而，并不是所有的行都匹配到不能只是并排地敲打桌子.对于没有 Result 的列，我希望显示 NULL 以填充行以使它们的记录数相同.

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.

我查找了 PIVOT，但我认为这在这里行不通……这种类型的数据转换称为什么?我不认为这是一个交叉表...

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...

谢谢！

答

您可以使用条件聚合进行交叉表:

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

相关推荐