了解 T-SQL 中的 PIVOT 函数
我对 SQL 很陌生.
I am very new to SQL.
我有一张这样的桌子:
ID | TeamID | UserID | ElementID | PhaseID | Effort
-----------------------------------------------------
1 | 1 | 1 | 3 | 5 | 6.74
2 | 1 | 1 | 3 | 6 | 8.25
3 | 1 | 1 | 4 | 1 | 2.23
4 | 1 | 1 | 4 | 5 | 6.8
5 | 1 | 1 | 4 | 6 | 1.5
我被告知要获取这样的数据
And I was told to get data like this
ElementID | PhaseID1 | PhaseID5 | PhaseID6
--------------------------------------------
3 | NULL | 6.74 | 8.25
4 | 2.23 | 6.8 | 1.5
我知道我需要使用 PIVOT 功能.但是不能清楚的理解.如果有人可以在上述情况下解释它，那将是很大的帮助.(或任何替代方案，如果有的话)
I understand I need to use PIVOT function. But can't understand it clearly. It would be great help if somebody can explain it in above case.(or any alternatives if any)
推荐答案
A PIVOT 用于将数据从一列旋转到多列.
A PIVOT used to rotate the data from one column into multiple columns.
对于您的示例，这里是一个 STATIC Pivot，这意味着您对要旋转的列进行硬编码:
For your example here is a STATIC Pivot meaning you hard code the columns that you want to rotate:
create table temp
(
id int,
teamid int,
userid int,
elementid int,
phaseid int,
effort decimal(10, 5)
)
insert into temp values (1,1,1,3,5,6.74)
insert into temp values (2,1,1,3,6,8.25)
insert into temp values (3,1,1,4,1,2.23)
insert into temp values (4,1,1,4,5,6.8)
insert into temp values (5,1,1,4,6,1.5)
select elementid
, [1] as phaseid1
, [5] as phaseid5
, [6] as phaseid6
from
(
select elementid, phaseid, effort
from temp
) x
pivot
(
max(effort)
for phaseid in([1], [5], [6])
)p
这是一个带有工作版本的 SQL 演示.
Here is a SQL Demo with a working version.
这也可以通过动态 PIVOT 来完成，您可以在其中动态创建列列表并执行 PIVOT.
This can also be done through a dynamic PIVOT where you create the list of columns dynamically and perform the PIVOT.
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.phaseid)
FROM temp c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT elementid, ' + @cols + ' from
(
select elementid, phaseid, effort
from temp
) x
pivot
(
max(effort)
for phaseid in (' + @cols + ')
) p '
execute(@query)
两者的结果:
ELEMENTID PHASEID1 PHASEID5 PHASEID6
3 Null 6.74 8.25
4 2.23 6.8 1.5
相关文章