转置/将不同的行属性设置为列,并将另一个属性分组? [英] Transpose / Pivot distinct row attribute as column and group another attribute?
本文介绍了转置/将不同的行属性设置为列,并将另一个属性分组?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
可能重复:
SQL Server动态PIVOT查询?
Possible Duplicate:
SQL Server dynamic PIVOT query?
是否可以对下表执行查询:
Is it possible to execute a query on the following table:
Game Player Goals
----- ------ ------
Game1 John 1
Game1 Paul 0
Game1 Mark 2
Game1 Luke 1
Game2 John 3
Game2 Paul 1
Game2 Luke 1
Game3 John 0
Game3 Mark 2
给出如下结果:
Game John Paul Mark Luke
----- ---- ---- ---- ----
Game1 1 0 2 1
Game2 3 1 - 1
Game3 0 - 2 -
它将每个不同的玩家变成一列,并对游戏进行分组,从而为每个玩家每个游戏提供目标.
It turns each distinct player into a column and groups the games gives goals per game per player.
推荐答案
您可以使用PIVOT
函数.如果您知道已知的列数,则可以对值进行硬编码:
You can use the PIVOT
function. If you have a known number of columns, then you can hard-code the values:
select *
from
(
select game, player, goals
from yourtable
) src
pivot
(
sum(goals)
for player in ([John], [Paul], [Mark], [Luke])
) piv
order by game
请参见带有演示的SQL小提琴
如果列数未知,则可以使用动态sql:
If you have an unknown number of columns, then you can use dynamic sql:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(player)
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT game, ' + @cols + ' from
(
select game, player, goals
from yourtable
) x
pivot
(
sum(goals)
for player in (' + @cols + ')
) p '
execute(@query)
请参见带有演示的SQL提琴
这篇关于转置/将不同的行属性设置为列,并将另一个属性分组?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文