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
4
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
1
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 |
#1
4
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
1
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 |