在SQL Server中透视固定的多列表 [英] Pivot a fixed multiple column table in sql server
本文介绍了在SQL Server中透视固定的多列表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个表需要报告服务使用:
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
您可以为此创建动态SQL,如下所示:
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
这篇关于在SQL Server中透视固定的多列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文