SQL Server: Dynamic pivot with headers to include

I am trying to use dynamic pivot and need help on converting rows to columns

The table looks like:

ID   expense  revenue date
1    43       45      12-31-2012   
1    32       32      01-01-2013 
3    64       56      01-31-2013
4    31       32      02-31-2013

and I need for reporting purposes like

ID  expense12-31-2012  expense01-01-2013 expense01-31-2013 revenue12-31-2013
1   43                 32                
3                                        64

In order to get both the expense and revenue columns as headers with the date, I would recommend applying both the UNPIVOT and the PIVOT functions.

The UNPIVOT will convert the expense and revenue columns into rows that you can append the date to. Once the date is added to the column names, then you can apply the PIVOT function.

The UNPIVOT code will be:

select id, 
  col+'_'+convert(varchar(10), date, 110) new_col, 
  value
from yt
unpivot
(
  value
  for col in (expense, revenue)
) un

See SQL Fiddle with Demo. This produces a result:

| ID |            NEW_COL | VALUE |
-----------------------------------
|  1 | expense_12-31-2012 |    43 |
|  1 | revenue_12-31-2012 |    45 |
|  2 | expense_01-01-2013 |    32 |

As you can see the expense/revenue columns are now rows with a new_col that has been created by concatenating the date to the end. This new_col is then used in the PIVOT:

select id,
  [expense_12-31-2012], [revenue_12-31-2012],
  [expense_01-01-2013], [revenue_01-01-2013],
  [expense_01-31-2013], [revenue_01-31-2013],
  [expense_03-03-2013], [revenue_03-03-2013]
from
(
  select id, 
    col+'_'+convert(varchar(10), date, 110) new_col, 
    value
  from yt
  unpivot
  (
    value
    for col in (expense, revenue)
  ) un
) src
pivot
(
  sum(value)
  for new_col in ([expense_12-31-2012], [revenue_12-31-2012],
                  [expense_01-01-2013], [revenue_01-01-2013],
                  [expense_01-31-2013], [revenue_01-31-2013],
                  [expense_03-03-2013], [revenue_03-03-2013])
) piv;

See SQL Fiddle with Demo.

The above version will work great if you have a known number of dates to turn into columns but if you have an unknown number of dates, then you will want to use dynamic SQL to generate the result:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(c.col+'_'+convert(varchar(10), yt.date, 110)) 
                    from yt
                    cross apply
                    (
                      select 'expense' col union all
                      select 'revenue'
                    ) c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id,' + @cols + ' 
            from
            (
              select id, 
                col+''_''+convert(varchar(10), date, 110) new_col, 
                value
              from yt
              unpivot
              (
                value
                for col in (expense, revenue)
              ) un
            ) src
            pivot 
            (
                sum(value)
                for new_col in (' + @cols + ')
            ) p '

execute(@query);

See SQL Fiddle with Demo. Both queries generate the same result.