Вот твой стол
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
Результат
![введите здесь описание изображения](https://i.stack.imgur.com/X8uQJ.jpg)
Вот скрипт http://sqlfiddle.com/#!3/95111/1 (Если при загрузке возникает какая-либо ошибка, нажмите RUNSQL)
person
Sarath KS
schedule
10.12.2014