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 |