时间戳

首页 > 解决方案 > 基于两列的展开数据表，SQL server

sql-server - 基于两列的展开数据表，SQL server

问题描述

我有一张我想重新塑造的桌子，目前看起来像这样：

ID    Year    Channel    Payments
 1    2012         HV         100
 1    2014         HV          56
 2    2012         NL       17000
 2    2012         HV         495
 3    2013         HV         565

等等......有很多行。

我需要重新塑造数据，以便每个 ID 有一行，并且多列显示每个年份和渠道中的付款。例如，上表如下所示：

ID   HV2012   HV2013   HV2014   NL2012
 1      100     NULL       56     NULL
 2      495     NULL     NULL    17000
 3     NULL      565     NULL     NULL

过去我在 R 中使用 dplyr 可以很容易地做到这一点，但现在需要使用 SQL 服务器。在不必指定每个新列的名称的情况下，我无法找到一种方法来执行此操作——因为我的数据涵盖了许多频道和年份，这实际上并不可行。我知道它可能需要动态 SQL，但我没有使用它的经验。

谢谢

标签： sql-serverpivotdynamic-sql

解决方案

您可以使用动态数据透视查询来实现此目的。

-- data

create table testTable
(
    Id int,
    Year int,
    Channel varchar(10),
    Payments int
)

 insert into testTable values (1,2012,'HV',100)
 insert into testTable values (1,2014,'HV',56)
 insert into testTable values (2,2012,'NL',17000)
 insert into testTable values (2,2012,'HV',495)
 insert into testTable values (3,2013,'HV',565)


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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(CONCAT(c.Channel,c.Year))
            FROM testTable c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT Id, ' + @cols + ' from 
            (
                select Id
                    , CONCAT(Channel,Year ) YC
                    , Payments
                from testTable
           ) x
            pivot 
            (
                 max(Payments)
                for YC in (' + @cols + ')
            ) p '

execute(@query)

推荐阅读