在 SQL Server (PIVOT) 中将行值转换为列 [英] Convert row value in to column in SQL server (PIVOT)
本文介绍了在 SQL Server (PIVOT) 中将行值转换为列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在 SQL Server 中有一个名为 test 的表,它有 3 列
I have table in SQL Server called test having 3 column
| ITEM | ATTRIBUTE | VALUE |
-----------------------------
| item1 | Quality | A |
| item1 | color | Red |
| item2 | Quality | B |
| item2 | color | Black |
我想要这样的输出:
| ITEM | QUALITY | COLOR |
---------------------------
| item1 | A | Red |
| item2 | B | Black |
如何在 SQL Server 中获取此信息.
How can I get this in SQL Server.
推荐答案
试试这个:
SELECT *
FROM (SELECT Item, attribute, value FROM MyTable) AS t
PIVOT
(
MAX(value)
FOR attribute IN([Quality], [Color])
) AS p;
输出:
╔═══════╦═════════╦═══════╗
║ ITEM ║ QUALITY ║ COLOR ║
╠═══════╬═════════╬═══════╣
║ item1 ║ A ║ Red ║
║ item2 ║ B ║ Black ║
╚═══════╩═════════╩═══════╝
请参阅 此 SQLFiddle
如果不知道attribute
的具体值也可以使用这个动态查询:
See this SQLFiddle
You can also use this dynamic query if you don't know the specific value of attribute
:
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(attribute)
from MyTable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT Item,' + @cols + '
from
(
Select Item, attribute , value
from MyTable
) dta
pivot
(
MAX(Value)
for attribute in (' + @cols + ')
) pvt '
execute(@query);
这篇关于在 SQL Server (PIVOT) 中将行值转换为列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文