理解 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

这篇关于理解 T-SQL 中的 PIVOT 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！