这会做您想要的，但是您必须指定所有日期
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 tselect @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
