无法使用我的查询数据透视表? [英] Cannot pivot table with my query?

问题描述

我有一张看起来像这样的桌子 -

I have a table which looks like this -

Id  AttributeName   AttributeValue
A1  Atr1            A1V1
A1  Atr2            A1V2
A1  Atr3            A1V3
A2  Atr1            A2V1
A2  Atr2            A2V2
A2  Atr3            A3V3

此表中的每个 ID 都具有完全相同的属性，即 ATR1、ATR2、ATR3.这些属性的值是唯一的.

Each ID in this table has the exact same attributes, ie ATR1, ATR2, ATR3. The values of these attributes is unique.

我想旋转这个表并获得以下输出 -

I want to pivot this table and get the following output -

Id  Atr1 Atr2 Atr3
A1  A1V1 A1V2 A1V3
A2  A2V1 A2V2 A2V3

我该怎么做?

我尝试了一个查询，但失败并显示错误 - Msg 156, Level 15, State 1, Line 21关键字FOR"附近的语法不正确.

I tried a query and it failed with the error - Msg 156, Level 15, State 1, Line 21 Incorrect syntax near the keyword 'FOR'.

-- Create a temporary table
DECLARE @MyTable TABLE
(Id varchar(25),
 AttributeName varchar(30),
 AttributeValue varchar(30))
-- Load Sample Data
INSERT INTO @MyTable VALUES ('A1', 'Atr1', 'A1V1')
INSERT INTO @MyTable VALUES ('A1', 'Atr2', 'A1V2')
INSERT INTO @MyTable VALUES ('A1', 'Atr3', 'A1V3')
INSERT INTO @MyTable VALUES ('A2', 'Atr1', 'A2V1')
INSERT INTO @MyTable VALUES ('A2', 'Atr2', 'A2V2')
INSERT INTO @MyTable VALUES ('A2', 'Atr3', 'A3V3')
SELECT Id, [Atr1], [Atr2],[Atr3]
FROM
( 
SELECT ID, AttributeName, AttributeValue
FROM @MyTable) AS SourceTable 
PIVOT 
(
    AttributeValue
    FOR AttributeName IN ([ATR1], [ATR2], [ATR3])
) AS pvt

推荐答案

只是为了扩展其他答案，PIVOT 函数需要某种类型的聚合.由于要从行转换为列的值是字符串，因此您只能使用 max() 或 min() 聚合函数.

Just to expand on the other answers, the PIVOT function requires some type of aggregation. Since the value that you want to convert from a row into a column is a string, then you are limited to using either the max() or min() aggregate function.

虽然 @Muhammed Ali 的 答案在您只有一个 AttributeName/AttributeValue 对，如果每个 ID 有多个对，那么您将只返回 max 或 min 值.

While @Muhammed Ali's answer will work when you have a single AttributeName/AttributeValue pair, if you have multiple pairs for each ID, then you will only return either the max or min value.

例如，如果您的样本数据是:

For example if your sample data is:

INSERT INTO @MyTable VALUES ('A1', 'Atr1', 'A1V1');
INSERT INTO @MyTable VALUES ('A1', 'Atr1', 'A1V4');
INSERT INTO @MyTable VALUES ('A1', 'Atr2', 'A1V2');
INSERT INTO @MyTable VALUES ('A1', 'Atr3', 'A1V3');
INSERT INTO @MyTable VALUES ('A2', 'Atr1', 'A2V1');
INSERT INTO @MyTable VALUES ('A2', 'Atr2', 'A2V2');
INSERT INTO @MyTable VALUES ('A2', 'Atr3', 'A3V3');

即使 A1 和 Atr1 的组合有多行，其他查询也只返回 max(attributevalue):

Even though you have multiple rows for the combination of A1 and Atr1, the other queries are only returning the max(attributevalue):

| ID | ATR1 | ATR2 | ATR3 |
|----|------|------|------|
| A1 | A1V4 | A1V2 | A1V3 |
| A2 | A2V1 | A2V2 | A3V3 |

我猜您实际上想要返回所有组合.我建议扩展您的查询以在您的查询中包含窗口函数 row_number().此查询会生成一个唯一值，然后该值将包含在 PIVOT 的分组方面，并允许您为每个 ID 返回多于一行.

I would guess that you would actually want to return all of the combinations. I suggest expanding your query to include the windowing function, row_number() in your query. This query generates a unique value that will then be included in the grouping aspect of the PIVOT and will allow you to return more than one row for each ID.

通过添加row_number()，查询将类似于以下内容:

By adding the row_number(), the query will be similar to the following:

SELECT Id, [Atr1], [Atr2],[Atr3]
FROM
( 
  SELECT ID, AttributeName, AttributeValue,
    row_number() over(partition by id, attributename
                      order by attributevalue) seq
  FROM @MyTable
) AS SourceTable 
PIVOT 
(
    max(AttributeValue)
    FOR AttributeName IN ([ATR1], [ATR2], [ATR3])
) AS pvt
order by id;

请参阅SQL Fiddle with Demo.您将获得返回所有行的结果:

See SQL Fiddle with Demo. You will get a result that returns all rows:

| ID | ATR1 |   ATR2 |   ATR3 |
|----|------|--------|--------|
| A1 | A1V1 |   A1V2 |   A1V3 |
| A1 | A1V4 | (null) | (null) |
| A2 | A2V1 |   A2V2 |   A3V3 |

如果您无法理解 PIVOT 的概念，那么我建议您考虑使用聚合函数和 CASE 表达式的组合来获得结果.然后就可以看到sequence/id的分组了:

If you are having trouble grasping the concept of PIVOT, then I would suggest look at using a combination of an aggregate function with a CASE expression to get the result. You can then see the grouping of the sequence/id:

SELECT Id, 
  max(case when attributename = 'Atr1' then attributevalue end) Atr1,
  max(case when attributename = 'Atr2' then attributevalue end) Atr2,
  max(case when attributename = 'Atr3' then attributevalue end) Atr3
FROM
( 
  SELECT ID, AttributeName, AttributeValue,
    row_number() over(partition by id, attributename
                      order by attributevalue) seq
  FROM @MyTable
) AS SourceTable 
group by id, seq

参见SQL Fiddle with Demo

这篇关于无法使用我的查询数据透视表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！