I have a table which I need to pivot for reporting services:
DateCreated Rands Units Average Price Success % Unique Users
-------------------------------------------------------------------------
2013-08-26 0 0 0 0 0
2013-08-27 0 0 0 0 0
2013-08-28 10 2 5 100 1
2013-08-29 12 1 12 100 1
2013-08-30 71 9 8 100 1
2013-08-31 0 0 0 0 0
2013-09-01 0 0 0 0 0
In other words I need to have Rands, Units, Average Price etc at rows and the dates as columns.
I have read various examples but I just can't seem to get it right. Any help would be much appreciated!
This one will do what you want, but you have to specify all the dates
select
c.Name,
max(case when t.DateCreated = '2013-08-26' then c.Value end) as [2013-08-26],
max(case when t.DateCreated = '2013-08-27' then c.Value end) as [2013-08-27],
max(case when t.DateCreated = '2013-08-28' then c.Value end) as [2013-08-28],
max(case when t.DateCreated = '2013-08-29' then c.Value end) as [2013-08-29],
max(case when t.DateCreated = '2013-08-30' then c.Value end) as [2013-08-30],
max(case when t.DateCreated = '2013-08-31' then c.Value end) as [2013-08-31],
max(case when t.DateCreated = '2013-09-01' then c.Value end) as [2013-09-01]
from test as t
outer apply (
select 'Rands', Rands union all
select 'Units', Units union all
select 'Average Price', [Average Price] union all
select 'Success %', [Success %] union all
select 'Unique Users', [Unique Users]
) as C(Name, Value)
group by c.Name
You can create a dynamic SQL for this, something like this:
declare @stmt nvarchar(max)
select @stmt = isnull(@stmt + ',', '') +
'max(case when t.DateCreated = ''' + convert(nvarchar(8), t.DateCreated, 112) + ''' then c.Value end) as [' + convert(nvarchar(8), t.DateCreated, 112) + ']'
from test as t
select @stmt = '
select
c.Name, ' + @stmt + ' from test as t
outer apply (
select ''Rands'', Rands union all
select ''Units'', Units union all
select ''Average Price'', [Average Price] union all
select ''Success %'', [Success %] union all
select ''Unique Users'', [Unique Users]
) as C(Name, Value)
group by c.Name'
exec sp_executesql @stmt = @stmt