sql-server - SQLServer表行到列数据-数据透视表

我有一个如下的Excel工作表，我已经以规范化的形式将数据从此excel转储到数据库中（作为下面的示例数据）。



现在，我想从数据库数据中获得类似的excel视图。我试过了，但是格式错误。如果有人使用列名和内部联接给出与excel相同的结果视图，那就很好。

随着年度的增长，我不想刻骨铭心。

declare @tblyear table(id int, year int)
insert into @tblyear values (1,2012), (2,2013),(3,2014) ,(4,2015),(5,2016)

declare @ChargeableYearDetails table ( id int, year int, CumulativeHrs numeric(18,2), CumulativeChargeableHrs  numeric(18,2))

--take only 2 row year wise for the sample
insert into @ChargeableYearDetails values
(1, 1, 1657.75, 1243.50),
(2, 1, 3925.50, 3044.75),
(3, 2, 870.25, 568.25),
(4, 2, 2517.75, 1808.00),
(5, 3, 189.50, 99.00),
(6, 3, 1982.75, 1295.25),
(7, 4, 539.00, 351.00),
(8, 4, 2542.75, 1924.75),
(9, 5, 874.50, 596.50),
(9, 5, 2721.50, 2175.50)


select * from @tblyear
select * from @ChargeableYearDetails

/*I tried this , but given wrong result*/
select * from @ChargeableYearDetails
pivot
(
    max(CumulativeHrs)
    FOR year in ([1],[2],[3],[4],[5])
) as p

最佳答案

我的回答有点复杂，但是我应该发布它。我使用动态sql和数据透视。

DECLARE @columnsH nvarchar(500),
        @columnsCH nvarchar(500),
        @columns nvarchar(1000),
        @sql nvarchar(4000)

CREATE TABLE #tblyear (id int, [year] int)
INSERT INTO #tblyear VALUES (1,2012), (2,2013),(3,2014) ,(4,2015),(5,2016)

CREATE TABLE #ChargeableYearDetails (id int, [year] int, CumulativeHrs numeric(18,2), CumulativeChargeableHrs  numeric(18,2))
INSERT INTO #ChargeableYearDetails VALUES
(1, 1, 1657.75, 1243.50),(2, 1, 3925.50, 3044.75),(3, 2, 870.25, 568.25),
(4, 2, 2517.75, 1808.00),(5, 3, 189.50, 99.00),(6, 3, 1982.75, 1295.25),
(7, 4, 539.00, 351.00),(8, 4, 2542.75, 1924.75),(9, 5, 874.50, 596.50),
(9, 5, 2721.50, 2175.50)

SELECT @columnsH = STUFF((SELECT DISTINCT ',' + QUOTENAME('CumulativeHrsYY'+ CAST([Year] AS NVARCHAR(4))) FROM #tblyear FOR XML PATH('')),1,1,'')
SELECT @columnsCH = STUFF((SELECT DISTINCT ',' + QUOTENAME('CumulativeChargeableHrs'+ CAST([Year] AS NVARCHAR(4))) FROM #tblyear FOR XML PATH('')),1,1,'')
SELECT @columns = STUFF((SELECT DISTINCT ',' + QUOTENAME('CumulativeHrsYY'+ CAST([Year] AS NVARCHAR(4))) +',' + QUOTENAME('CumulativeChargeableHrs'+ CAST([Year] AS NVARCHAR(4))) FROM #tblyear FOR XML PATH('')),1,1,'')

SELECT @sql = '

SELECT '+ @columns+'
FROM (
SELECT *
FROM (
    SELECT ''CumulativeHrsYY''+ CAST(t.[Year] AS NVARCHAR(4)) as [Year],
            c.CumulativeHrs,
            ROW_NUMBER() OVER (PARTITION BY c.[year] ORDER BY c.[year]) as rn
    FROM #ChargeableYearDetails c
    LEFT JOIN #tblyear t
        ON t.ID = c.[year]
    ) as t
pivot
(
    max(CumulativeHrs)
    FOR [year] in ('+@columnsH+')
) as p
) as part1
LEFT JOIN (
SELECT *
FROM (
    SELECT ''CumulativeChargeableHrs''+ CAST(t.[Year] AS NVARCHAR(4)) as [Year],
            c.CumulativeChargeableHrs,
            ROW_NUMBER() OVER (PARTITION BY c.[year] ORDER BY c.[year]) as rn
    FROM #ChargeableYearDetails c
    LEFT JOIN #tblyear t
        ON t.ID = c.[year]
    ) as t
pivot
(
    max(CumulativeChargeableHrs)
    FOR [year] in ('+@columnsCH+')
) as p
) as part2
ON part1.rn = part2.rn'

EXEC(@sql)

DROP TABLE #ChargeableYearDetails
DROP TABLE #tblyear

输出：

CumulativeHrsYY2012 CumulativeChargeableHrs2012 CumulativeHrsYY2013 CumulativeChargeableHrs2013 CumulativeHrsYY2014 CumulativeChargeableHrs2014 CumulativeHrsYY2015 CumulativeChargeableHrs2015 CumulativeHrsYY2016 CumulativeChargeableHrs2016
1657.75             1243.50                     870.25              568.25                      189.50              99.00                       539.00              351.00                      874.50                  596.50
3925.50             3044.75                     2517.75             1808.00                     1982.75             1295.25                     2542.75             1924.75                     2721.50             2175.50

关于sql-server - SQLServer表行到列数据-数据透视表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37051655/