旋转两个列，使表中的其他列保持不变。

I'd like to know if the following could be done in SQL Server 2012.

我想知道下面是否可以在SQL Server 2012中完成。

I have the following data:

我有以下数据:

Product_ID    Date     Attribute    Value
-------------------------------------------
10025135       2009    Colour       Red
10025135       2009    Size         20 cm
10025135       2009    Material     Steel
10025135       2010    Colour       Green
10025135       2010    Size         NULL
10025135       2010    Material     Alloy
10025136       2009    Colour       Black
10025136       2009    Size         30cm
10025136       2009    Material     NULL

I want to retrieve the data so that they are displayed as follows:

我想要检索数据，以便它们显示如下:

Product_ID    Date    Colour    Size     Material
-------------------------------------------------
10025135       2009   Red       20 cm    Steel
10025135       2010   Green     NULL     Alloy
10025136       2009   Black     30 cm    NULL

I've tried to pivot them without success.

我试图使他们转向，但没有成功。

2 个解决方案

#1

SELECT   Product_ID, Date, Colour, Size, Material
FROM
        (
            SELECT  Product_ID, Date, Attribute, Value
            FROM    Table1
        ) org
        PIVOT
        (
            MAX(Value)
            FOR Attribute IN (Colour, Size, Material)
        ) pivotHeader

• SQLFiddle Demo
• SQLFiddle演示

OUTPUT

输出

╔════════════╦══════╦════════╦════════╦══════════╗
║ PRODUCT_ID ║ DATE ║ COLOUR ║  SIZE  ║ MATERIAL ║
╠════════════╬══════╬════════╬════════╬══════════╣
║   10025135 ║ 2009 ║ Red    ║ 20 cm  ║ Steel    ║
║   10025135 ║ 2010 ║ Green  ║ (null) ║ Alloy    ║
║   10025136 ║ 2009 ║ Black  ║ 30cm   ║ (null)   ║
╚════════════╩══════╩════════╩════════╩══════════╝

The other way of doing this is by using MAX() and CASE

另一种方法是使用MAX()和CASE

SELECT  Product_ID, DATE,
        MAX(CASE WHEN Attribute = 'Colour' THEN Value END ) Colour,
        MAX(CASE WHEN Attribute = 'Size' THEN Value END ) Size,
        MAX(CASE WHEN Attribute = 'Material' THEN Value END ) Material
FROM    Table1
GROUP   BY Product_ID, DATE

• SQLFiddle Demo
• SQLFiddle演示

#2

You can implement the using the PIVOT function to get the desired result. This will take the row values in the attribute column and convert them to columns.

您可以使用PIVOT函数实现，以获得所需的结果。这将获取属性列中的行值并将其转换为列。

The main thing to consider when doing this is whether you will have a known or unknown number of attribute values.

在执行此操作时，要考虑的主要问题是您是否拥有已知或未知的属性值。

If the values are known ahead of time, then you can hard-code the values using a static PIVOT:

如果这些值是预先知道的，那么您可以使用静态PIVOT对这些值进行硬编码:

select product_id,
  Colour,
  Size,
  Material
from 
(
  select product_id, date, attribute, value
  from yourtable
) src
pivot
(
  max(value)
  for attribute in (Colour, Size, Material)
) piv;

See SQL Fiddle with Demo.

参见SQL小提琴演示。

But if the values of the attribute column are unknown or will need to be dynamic, then you will need to implement dynamic SQL. The dynamic SQL will get the list of columns to be used in a SQL string.

但是，如果属性列的值是未知的，或者需要是动态的，则需要实现动态SQL。动态SQL将获得将在SQL字符串中使用的列列表。

The code to get the list of columns would be similar to this:

获取列列表的代码与以下类似:

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(attribute) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

The result of this query is:

这个查询的结果是:

[Colour],[Material],[Size] 

This creates on execution the list of attributes that should be converted to columns and it will be concatenated into the final string that is executed. You code for the dynamic SQL pivot is:

这将在执行时创建应该转换为列的属性列表，并将其连接到执行的最终字符串中。您的动态SQL pivot代码是:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(attribute) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT product_id,' + @cols + ' 
             from 
             (
                select product_id, date, attribute, value
                from yourtable
             ) x
             pivot 
             (
                max(value)
                for attribute in (' + @cols + ')
             ) p '

execute(@query)

See SQL Fiddle with Demo.

参见SQL小提琴演示。

The result for both queries is:

两个查询的结果是:

| PRODUCT_ID | COLOUR |   SIZE | MATERIAL |
-------------------------------------------
|   10025135 |    Red |  20 cm |    Steel |
|   10025136 |  Black |   30cm |   (null) |
|   10025135 |  Green | (null) |    Alloy |