sql-server - 如何在动态数据透视表中将两列组合成单列

Question

我有这样的模式

demo(id, val, month, year, decide)

演示数据和模式在这个 fiddle 中给出 http://sqlfiddle.com/#!3/dd89d5/1

在输出中，我想将行转换为列。在我想要的输出中，

ID    (11 14)  (12 14) (2 15) ...     decider
101    0.45     0.5      0.3          411
102    0.4      0.2      0.1          411

我要对月、年进行排序

我经历了很多线程并找到了一个 fiddle 。并尝试定制以满足我的需要

这是我尝试过的 http://sqlfiddle.com/#!3/dd89d5/1

但无法得到解决方案。请帮助我

Answer 1

这是你的 table

create table demo(
    id varchar(max),    val decimal(4,2),    month int,    year int, decider int
  )

INSERT INTO demo
    ([id], [val], [month], [year], [decider])
VALUES

(101, 0.25, 11, 14, 411),
(101, 1, 12, 14, 411),
(101, 0.5, 1, 15, 411),
(101, 0.75, 2, 15, 411),
(102, 0.25, 11, 14, 411),
(102, 0.5, 12, 14, 411),
(102, 0.25, 1, 15, 411),
(101, 0.75, 11, 14, 412),
(101, 0.5, 1, 15, 412),
(101, 0.25, 2, 15, 412),
(102, 0.5, 11, 14, 412),
(102, 0.5, 12, 14, 412),
(103, 0.25, 1, 15, 412),
(103, 0.5, 11, 14, 411)
;

使用Dense_Rank 对数据透视列进行排序

SELECT DENSE_RANK() OVER(ORDER BY [YEAR] ,[MONTH]) RNO,*,CAST([MONTH] AS VARCHAR) + ' ' +  CAST([YEAR] AS VARCHAR) DT
INTO #TEMP
FROM
(
    SELECT ID,SUM(VAL)VAL,[MONTH],[YEAR],DECIDER
    FROM DEMO 
    GROUP BY ID,[MONTH],[YEAR],DECIDER
)TAB

选择数据透视表的列并声明一个变量以将 NULL 替换为零

DECLARE @cols NVARCHAR (MAX)
DECLARE @NullToZeroCols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + DT + ']', 
              '[' + DT + ']')
               FROM    (SELECT DISTINCT RNO,DT FROM #TEMP) PV  
               ORDER BY RNO

SET @NullToZeroCols = SUBSTRING((SELECT ',ISNULL(['+DT+'],0) AS ['+DT+']' 
FROM(SELECT DISTINCT DT,RNO FROM #TEMP GROUP BY DT,RNO)TAB  
ORDER BY RNO  FOR XML PATH('')),2,8000)

现在转动它

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT P.ID,' + @NullToZeroCols + ',DECIDER FROM 
             (
                 SELECT ID, DT, val,DECIDER FROM #TEMP
             ) x
             PIVOT 
             (
                 SUM(val)
                 FOR DT IN (' + @cols + ')
            ) p
            ORDER BY ID;' 

EXEC SP_EXECUTESQL @query

结果

这是 fiddle http://sqlfiddle.com/#!3/95111/1 (如果加载时出现任何错误，请按 RUNSQL)