TSql 复杂枢轴

2022-01-22 00:00:00 pivot pivot-table sql tsql sql-server

我有一张这样的桌子......

I have a table like this...

LEVEL        Action         Date             User
--------------------------------------------------
1            Approve        01/01/2013       User1
2            Approve        02/01/2013       User2
3            Rejected       03/01/2013       User3
1            Approve        04/01/2013       User1
2            Approve        05/01/2013       User2
3            Approve        06/01/2013       User3
.                .              .              .
.                .              .              .
.                .              .              .

我想要这个……

这可以使用 PIVOT 吗?

Is this possible using PIVOT?

LEVEL1 - User 1           LEVEL2 - User 2                  LEVEL3 - User 3
---------------------------------------------------------------------------
01/01/2013 - Approve      02/01/2013 - Approve             03/01/2013 - Rejected
04/01/2013 - Approve      05/01/2013 - Approve             06/01/2013 - Approve
         .                        .                                .
         .                        .                                .

注意:级别数是动态的.例如完全批准一个项目可以是5个级别，6个级别等.所以数据透视表的列数是动态的.

推荐答案

是的，这可以使用 PIVOT 函数，我首先建议查看查询的硬编码版本，以便在移动到动态版本之前查看查询的编写方式查询.

Yes, this can be done using the PIVOT function, I would first suggest looking at a hard-coded version of the query so you can see how the query is written before moving to a dynamic version of the query.

静态版本类似于以下内容:

A static version will be similar to the following:

select [Level1 - User1], [Level2 - User2], [Level3 - User3]
from
(
  select 'Level'+cast(level as varchar(1)) + ' - '+ [user] col, 
    convert(varchar(10), date, 101) +' - '+ action value,
    row_number() over(partition by level order by [user], date) rn
  from yt
) d
pivot
(
  max(value)
  for col in ([Level1 - User1], [Level2 - User2], [Level3 - User3])
) piv;

参见 SQL Fiddle with Demo.您会注意到 level 和 user 列被连接以创建新列，而 date 和 action连接起来为每列创建值.我还添加了一个 row_number() 来为每一行创建一个唯一值，这在您在 PIVOT 中应用聚合函数时非常重要.如果你不使用它，那么你将只得到一行.

See SQL Fiddle with Demo. You will notice that the level and user columns are concatenated to create the new columns, and the date and action are concatenated to create the value for each column. I also added a row_number() to create a unique value for each row, this will be important when you apply the aggregate function in the PIVOT. If you do not use this, then you will get only one row as a result.

由于您现在有一个工作版本，因此可以轻松地将其转换为动态版本:

Since you now have a working version, this can be converted to a dynamic version easily:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Level'+cast(level as varchar(1)) + ' - '+ [user]) 
                    from yt
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ' + @cols + ' from 
             (
                select ''Level''+cast(level as varchar(1)) + '' - ''+ [user] col, 
                  convert(varchar(10), date, 101) +'' - ''+ action value,
                  row_number() over(partition by level order by [user], date) rn
                from yt
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

execute(@query);

参见 SQL Fiddle with Demo.两者的结果都是:

See SQL Fiddle with Demo. The result for both is:

|       LEVEL1 - USER1 |       LEVEL2 - USER2 |        LEVEL3 - USER3 |
-----------------------------------------------------------------------
| 01/01/2013 - Approve | 02/01/2013 - Approve | 03/01/2013 - Rejected |
| 04/01/2013 - Approve | 05/01/2013 - Approve |  06/01/2013 - Approve |

相关文章