Sqlserver 表行到列数据-数据透视表 [英] Sqlserver table Row to column data - pivot table

问题描述

我有一个如下所示的 Excel 工作表，我已经将数据以标准化形式从这个 excel 转储到数据库中(如下面的示例数据).

I have an Excel-sheet as below, I already dump the data into database (as the sample data below) from this excel in normalized form.

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

Now I want to get the similar view of excel from database's data. I tried this, but given in wrong format. Good if somebody given the same result view as excel with column name and inner join.

随着时间的推移，我不想硬核.

I do not want to hardcore as the year expand.

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 和透视.

My answer is a little bit complicated, but I should post it. I use dynamic sql and pivoting.

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

这篇关于Sqlserver 表行到列数据-数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！