Return Columns As Rows based on Number of Months

Question

This seems like a simple thing to accomplish but I'm not sure if I am thinking about it correctly to get the desired results. I'm using a pivot but I think I need something else paired with it.

I have an invoice table that contains monthly invoices for each client. At most, a 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. There would be a column for each month. So Client 1 would look like:

+--------------+----+----+---+---+---+---+---+---+---+----+----+----+-------+
|  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 both the UNPIVOT and PIVOT function in SQL Server. If you have a known number of columns, then you can use a 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 it might be considerably easier to use dynamic sql to perform this operation, then there is less code to write and this will adjust the number of months based on what you have in your data sample:

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 produce the same results, the difference is that the second will adjust 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 this

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