How do I combine two columns into single column in dynamic pivot

Question

I have a schema like this

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

Demo data and schema is given in this fiddle http://sqlfiddle.com/#!3/dd89d5/1

In output i want to transform the rows to columns. In the output i want,

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

I want the month, year to be sorted

I went through lot of threads and found a fiddle. And tried to customize to suit my need

Here is what i tried http://sqlfiddle.com/#!3/dd89d5/1

But could not get the solution. Please help me

Sarath Subramanian · Accepted Answer · 2014-12-11T08:58:06.760

Here is your 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)
;

Use Dense_Rank to order the column for pivot

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

Select the columns for pivot and declare a variable to replace NULL with zero

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)

Now pivot it

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

Result

enter image description here

Here is the Fiddle http://sqlfiddle.com/#!3/95111/1 (If any error occur on load press RUNSQL)

I have added the code to replace NULL with zero as per your updation @sanjay — Sarath Subramanian, Dec 11 '14 at 08:58

How do I combine two columns into single column in dynamic pivot

1 Answers1