在SQL Server中透视固定的多列表

admin

在SQL Server中透视固定的多列表

sql

我有一个表需要报告服务使用：

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

换句话说，我需要在行中有兰德，单位，平均价格等，而在列中有日期。

我已经阅读了各种示例，但似乎似乎做对了。任何帮助将非常感激！

阅读 135

2021-06-07

共1个答案

admin

这会做您想要的，但是您必须指定所有日期

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，如下所示：

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

2021-06-07