在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.

换句话说,我需要在行中使用Rands,Units,Average Price等,并将日期作为列。

I have read various examples but I just can't seem to get it right. Any help would be much appreciated!

我已经阅读了各种例子,但我似乎无法做到正确。任何帮助将非常感激!

2 个解决方案

#1

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:

您可以为此创建动态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

#2

I solved this in the end using dynamic sql, very similar to the marked answer. I wasn't able to find a way of doing this without dynamic sql. The dates had to be in order and the last 7 days, they also had to use the day of the week names (which I didn't specify in the question).

我最后使用动态sql解决了这个问题,非常类似于标记的答案。没有动态sql,我无法找到这样做的方法。日期必须是有序的,最后7天,他们还必须使用星期几的名称(我没有在问题中指明)。

The biggest change I needed to make was changing the table variable into a temporary table. This is because dynamic sql statements execute in a different context and don't know about any variables you have created.

我需要做的最大的改变是将表变量更改为临时表。这是因为动态sql语句在不同的上下文中执行,并且不知道您创建的任何变量。

In the end I was completely off track trying to use PIVOT and APPLY should be used in situations where there are more than one "type" of value.

最后,我完全偏离试图使用PIVOT,应该在有多种“类型”值的情况下使用APPLY。

I have included my solution below since it could help someone who has a similar problem:

我已经在下面提供了我的解决方案,因为它可以帮助有类似问题的人:

CREATE TABLE #SummaryTable
(
    [DateCreated] DATE UNIQUE,
    [Rands] DECIMAL,
    [Units] INT,
    [Average Price] DECIMAL,
    [Success %] INT,
    [Unique Users] INT
);

--Code to fill table

declare @stmt nvarchar(max)

select @stmt = isnull(@stmt + ',', '') + 
    'max(case when t.DateCreated = ''' + convert(nvarchar(16), t.DateCreated, 126)
 + ''' then c.Value end) as [' + left(datename(dw, t.DateCreated),3) + ']'
from #SummaryTable as t

select @stmt = '
   select
       c.Name, ' + @stmt + ' from #SummaryTable 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(@stmt)

#1