Return Columns as Rows by Months

Question

This seems like a simple task, but I'm not sure if I think about it correctly to get the desired results. I use the rod, but I think I need something else paired with it.

I have an invoice table that contains monthly invoices for each customer. In the best case, the client will have 12 invoices per year, 1 for each month.

+----------+-------+-------+--------------+--------------+--------------+ | ClientID | Month | Year | ColumnValue1 | ColumnValue2 | ColumnValue3 | +----------+-------+-------+--------------+--------------+--------------+ | 1 | 1 | 2012 | 20 | 30 | 50 | | 1 | 2 | 2012 | 25 | 35 | 40 | | 2 | 1 | 2012 | 28 | 38 | 48 | +----------+-------+-------+--------------+--------------+--------------+ 

Now I want to create a list like below based on each client. For each month there will be a column. Thus, client 1 will look like this:

 +--------------+----+----+---+---+---+---+---+---+---+----+----+----+-------+ | ColumnName | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | Total | +--------------+----+----+---+---+---+---+---+---+---+----+----+----+-------+ | ColumnValue1 | 20 | 25 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 45 | | ColumnValue2 | 30 | 35 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 65 | | ColumnValue3 | 50 | 40 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 90 | +--------------+----+----+---+---+---+---+---+---+---+----+----+----+-------+ 

Answer 1

This can be done using the UNPIVOT and PIVOT in SQL Server. If you have a known number of columns, you can use the static version:

 select clientid, col, year, isnull([1], 0) [1], isnull([2], 0) [2], isnull([3], 0) [3], isnull([4], 0) [4], isnull([5], 0) [5], isnull([6], 0) [6], isnull([7], 0) [7], isnull([8], 0) [8], isnull([9], 0) [9], isnull([10], 0) [10], isnull([11], 0) [11], isnull([12], 0) [12], (isnull([1], 0) + isnull([2], 0) + isnull([3], 0) + isnull([4], 0) + isnull([5], 0) + isnull([6], 0) + isnull([7], 0) + isnull([8], 0) + isnull([9], 0) + isnull([10], 0) + isnull([11], 0) + isnull([12], 0) ) Total from ( select clientid, col, month, year, value from yourtable unpivot ( value for col in (ColumnValue1, ColumnValue2, ColumnValue3) ) u ) x pivot ( sum(value) for month in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]) ) p 

See SQL Fiddle with Demo

But for this operation it can be much easier to use dynamic sql, then there is less code to write, and this will change the number of months based on what you have in your example data:

 DECLARE @colsUnpivot AS NVARCHAR(MAX), @query AS NVARCHAR(MAX), @colsPivot as NVARCHAR(MAX), @colsTotal as NVARCHAR(MAX), @colsNull as NVARCHAR(MAX) select @colsUnpivot = stuff((select ','+ quotename(C.name) from sys.columns as C where C.object_id = object_id('yourtable') and C.name like 'ColumnValue%' for xml path('')), 1, 1, '') select @colsPivot = STUFF((SELECT distinct ', ' + quotename(Month) from yourtable t FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') select @colsNull = STUFF((SELECT distinct ', IsNull(' + quotename(Month) + ', 0) as '+quotename(Month) from yourtable t FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') select @colsTotal = STUFF((SELECT distinct '+ IsNull(' + quotename(Month) + ', 0)' from yourtable t FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'select clientid, year, ' +@colsNull +', ' +@colsTotal +' as Total from ( select clientid, col, month, year, value from yourtable unpivot ( value for col in (' +@colsUnpivot +') ) u ) x pivot ( sum(value) for month in('+ @colspivot +') ) p' exec(@query) 

See SQL Fiddle with Demo

Both will give the same results, the difference is that the second will be adjusted based on the data in your table:

 | CLIENTID | YEAR | 1 | 2 | TOTAL | ------------------------------------- | 1 | 2012 | 20 | 25 | 45 | | 1 | 2012 | 30 | 35 | 65 | | 1 | 2012 | 50 | 40 | 90 | | 2 | 2012 | 28 | 0 | 28 | | 2 | 2012 | 38 | 0 | 38 | | 2 | 2012 | 48 | 0 | 48 | 

Answer 2

try it

 Declare @t Table(ClientId Int,[Month] Int,[Year] Int,ColumnValue1 Int,ColumnValue2 Int, ColumnValue3 Int) Insert Into @t Values(1,1,2012,20,30,50),(1,2,3012,25,35,40),(2,1,2012,28,38,48) ;With Cte As ( Select ClientId,[Month],ColumnName,ColumnNameValues From @t UnPivot(ColumnNameValues For ColumnName In (ColumnValue1,ColumnValue2,ColumnValue3)) As unpvt ) Select ClientId, ColumnName ,[1] = Coalesce([1],0) ,[2] = Coalesce([2],0) ,[3] = Coalesce([3],0) ,[4] = Coalesce([4],0) ,[5] = Coalesce([5],0) ,[6] = Coalesce([6],0) ,[7] = Coalesce([7],0) ,[8] = Coalesce([8],0) ,[9] = Coalesce([9],0) ,[10]= Coalesce([10],0) ,[11]= Coalesce([11],0) ,[12] = Coalesce([12],0) ,Total = Coalesce([1],0) + Coalesce([2],0) + Coalesce([3],0) + Coalesce([4],0) + Coalesce([5],0) + Coalesce([6],0) + Coalesce([7],0) + Coalesce([8],0) + Coalesce([9],0) + Coalesce([10],0) + Coalesce([11],0) + Coalesce([12],0) From Cte PIVOT ( MAX(ColumnNameValues) For [Month] In ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12] ) ) As pvt --Where ClientId =1 -- uncomment for specific client report Order By 1 

Result

Answer 3

 Create table sequence (seqid bigint) go --Create a table which has sequence from 1 to 12 for monthId Insert into sequence Select Top 12 ROW_NUMBER() over(order by name) from sys.objects go USE tempdb GO CREATE TABLE TestReport ( ClientId int ,MonthId int ,YearId int ,val1 int ,val2 int ,val3 int ) go insert into TestReport Select 1, 1,2012, 20,30,50 union Select 1,2,2012,25, 35, 40 union Select 2, 1, 2012, 28,38,48 Select * from testReport --Cross join with the Sequence table to get rows for each month Select clientId , seqid as monthId , YearId , case when MonthId = seqid then val1 else 0 end val1 , case when MonthId = seqid then val2 else 0 end val2 , case when MonthId = seqid then val3 else 0 end val3 into #Temp from sequence seq cross join testReport rpt where seq.seqid <=12 --Select * from #Temp SELECT 'ColumnValue1' AS [columnName], [1], [2], [3], [4],[5],[6],[7],[8],[9],[10],[11],[12] ,[1]+ [2]+ [3]+ [4]+ [5]+ [6]+ [7]+ [8]+ [9]+ [10]+ [11]+ [12] as Total FROM (SELECT monthId, val1 FROM #Temp where ClientId =1 ) AS SourceTable PIVOT ( max(val1) FOR MonthId IN ( [1], [2], [3], [4],[5],[6],[7],[8],[9],[10],[11],[12]) ) AS PivotTable go Drop table #Temp Drop table sequence drop table TestReport go