如果没有PIVOT，则显示GROUP BY列为列名的数据

Question

我有如下数据：

Name    Date         Hr     Min    Amt
Joe     20150320     08     00     5
Joe     20150320     08     15     3
Carl    20150320     09     30     1
Carl    20150320     09     45     2
Ray     20150320     13     00     8
Ray     20150320     13     30     6

一个简单的GROUP BY[Name]、[Date]、[Hr]将按小时显示每个销售人员的总数。

如果不使用PIVOT或动态sql，我如何在以小时为列的地方显示这些数据？使用PIVOT，我需要详细说明每小时的情况，因此如果上面的数据是唯一的数据，那么将有3列包含数据（8、9、13），21列为空。

我之所以要这样做，是因为我想创建一个SSRS报告，其中的列是小时。不幸的是，我不能使用矩阵，因为我不能根据列的详细信息进行排序（即点击"8"，从最小到最大显示）；我已经向MS专家确认了这个限制。

因此，我们非常感谢您的帮助。我们有Sql Server 2008 R2。谢谢

Answer 1

Select Name    
      ,[Date]
      ,SUM(Case When Hr = '00' THEN Amt END) [00]
      ,SUM(Case When Hr = '01' THEN Amt END) [01]
      ,SUM(Case When Hr = '02' THEN Amt END) [02]
      ,SUM(Case When Hr = '03' THEN Amt END) [03]
      ,SUM(Case When Hr = '04' THEN Amt END) [04]
      ,SUM(Case When Hr = '05' THEN Amt END) [05]
      ,SUM(Case When Hr = '06' THEN Amt END) [06]
      ,SUM(Case When Hr = '07' THEN Amt END) [07]
      ,SUM(Case When Hr = '08' THEN Amt END) [08]
      ,SUM(Case When Hr = '09' THEN Amt END) [09]
      ,SUM(Case When Hr = '10' THEN Amt END) [10]
      ,SUM(Case When Hr = '11' THEN Amt END) [11]
      ,SUM(Case When Hr = '12' THEN Amt END) [12]
      ,SUM(Case When Hr = '13' THEN Amt END) [13]
      ,SUM(Case When Hr = '14' THEN Amt END) [14]
      ,SUM(Case When Hr = '15' THEN Amt END) [15]
      ,SUM(Case When Hr = '16' THEN Amt END) [16]
      ,SUM(Case When Hr = '17' THEN Amt END) [17]
      ,SUM(Case When Hr = '18' THEN Amt END) [18]
      ,SUM(Case When Hr = '19' THEN Amt END) [19] 
      ,SUM(Case When Hr = '20' THEN Amt END) [20]
      ,SUM(Case When Hr = '21' THEN Amt END) [21]
      ,SUM(Case When Hr = '22' THEN Amt END) [22]
      ,SUM(Case When Hr = '23' THEN Amt END) [23]
From TablenName
Group By Name ,[Date]

Answer 2

类似的工作？？将你的关键字段放入临时表中，不要加入它们，这样你就可以为所有内容创建一个可能性，然后在中子查询你的amt。

select * into #Temp1 from (
select '01' as 'Pivot_Hour'
union all
select '02' as 'Pivot_Hour'
union all 
select '03' as 'Pivot_Hour'
union all
select '04' as 'Pivot_Hour'
union all
select '05' as 'Pivot_Hour'
--Put all 24 hours in....
)  
x
select * into #Temp2 from (
select 'Carl' as 'User'
union all
select 'Joe' as 'User'
union all 
select 'Ray' as 'User'
union all
select 'Dave' as 'User'
union all
select 'Seve' as 'User'
---Could select distinct from your data...
)  
x

Select * into #Temp3 from (
select '20150321' as 'Date'
union all
select '20150322' as 'Date'
union all 
select '20150323' as 'Date'
union all
select '20150324' as 'Date'
union all
select '20150325' as 'Date'
---Could select distinct from your data...
)  
x
select * 
,(select sum(yrd.amt) from Yourdata Yrd 
where yrd.Name = t2.User 
and yrd.date = t3.date 
and yrd.Hr = t1.Pivot_Hour)Amt
from #Temp1 t1,#Temp2 t2,#Temp3 t3
drop table #Temp1
drop table #Temp2
drop table #Temp3