MSSQL 动态透视列值到列标题
有人可以帮我在 mssql 上转换以下内容吗?
Could someone help me convert the following on mssql please?
ID | PROPERTY_NAME | PROPERTY_VALUE
1 | name1 | value
1 | name2 | value
1 | name3 | value
2 | name4 | value
2 | name2 | value
3 | name6 | value
..
PROPERTY_NAME &PROPERTY_VALUE 是标题，id 有多个属性"
PROPERTY_NAME & PROPERTY_VALUE being the headers, id having multiple 'properties'
我想把它转换成:
ID | NAME1 | NAME2 | NAME3 | NAME4 | NAME5 | NAME6 | nameETC...
1 | value | value | value | | | | valueETC...
2 | | value | | value | | | valueETC...
3 | | | | | | value | valueETC...
..
在哪里 NAME1 |名称2 |NAME3 等现在是列标题.
Where NAME1 | NAME2 | NAME3 etc are now the column headers.
我猜是一个枢轴和一个从 MycoolTable 中选择不同的 PROPERTY_NAME"，但似乎无法将两者放在一起.
I'm guessing a pivot and a 'select distinct PROPERTY_NAME from MycoolTable' but can't seem to put the two together.
据我所知:(没有 server_id)列和所有地方的所有空值)显然我很愚蠢:D
This is as far as i got: (no server_id) column and all nulls everywhere) Clearly i'm stupid :D
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT distinct
',' + QUOTENAME(PROPERTY_NAME)
FROM [BSARA_DW_DB].[dbo].[SERVER_PROPERTY]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'');
SET @query = 'SELECT '+ @cols + ' from
(
SELECT SERVER_ID, PROPERTY_NAME, PROPERTY_CHAR_VAL
FROM [BSARA_DW_DB].[dbo].[SERVER_PROPERTY]
) x
pivot
(
MAX(SERVER_ID)
for PROPERTY_CHAR_VAL in (' + @cols + ')
) p ';
execute(@query)
非常感谢，迈克
推荐答案
您当前查询的问题在于:
The problem with your current query is with the line:
MAX(SERVER_ID)
您希望改为显示每个 PROPERTY_NAME 的 PROPERTY_CHAR_VAL.SERVER_ID 将作为列的最终结果的一部分.
You want to display the PROPERTY_CHAR_VAL for each PROPERTY_NAME instead. The SERVER_ID will be a part of the final result as a column.
有时当您使用 PIVOT 时，首先使用硬编码的值编写代码会更容易，类似于:
Sometimes when you are working with PIVOT is is easier to write the code first with the values hard-coded similar to:
select id, name1, name2, name3, name4
from
(
select id, property_name, property_value
from yourtable
) d
pivot
(
max(property_value)
for property_name in (name1, name2, name3, name4)
) piv;
请参阅 SQL Fiddle with Demo.
一旦你有了一个逻辑正确的版本，你就可以把它转换成动态 SQL 来得到结果.这将创建一个将被执行的 sql 字符串，它将包含您所有的新列名称.
Once you have a version that has the correct logic, then you can convert it to dynamic SQL to get the result. This will create a sql string that will be executed and it will include all of your new columns names.
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(PROPERTY_NAME)
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT id, ' + @cols + '
from
(
select id, property_name, property_value
from yourtable
) x
pivot
(
max(property_value)
for property_name in (' + @cols + ')
) p '
execute sp_executesql @query;
参见 SQL Fiddle with Demo.两者都会给出结果:
See SQL Fiddle with Demo. Both will give a result:
| ID | NAME1 | NAME2 | NAME3 | NAME4 | NAME6 |
|----|--------|--------|--------|--------|--------|
| 1 | value | value | value | (null) | (null) |
| 2 | (null) | value | (null) | value | (null) |
| 3 | (null) | (null) | (null) | (null) | value |
相关文章