四步搞定竖表变横表

做报表中经常有竖表变横表的需求。所谓竖表变横表，通常指下面的情况：

 

假设我们有一个客户订单表，包含客户号、产品号、订购日期、订购数量。这样存放基础数据是很普遍的做法。但有时候领导关心客户的订购情况（比如重要的大客户），希望直接显示客户对产品1、产品2、产品3。。。订购总量的报表。

 

我们来模拟一下这个场景。

 

1 建表
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[OrdersDemo](
 [ID] [uniqueidentifier] NOT NULL, -- 主键
 [CustID] [int] NOT NULL, -- 客户号
 [ProdID] [int] NOT NULL, -- 产品号
 [Qty] [int] NOT NULL,  -- 订购数量
 [OrderTime] [datetime] NOT NULL, -- 订购日期
 CONSTRAINT [PK_OrdersDemo] PRIMARY KEY CLUSTERED
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

 

2 随便造些测试数据
insert dbo.OrdersDemo (ID,CustID,ProdID,Qty,OrderTime)
values (NEWID(),1,1,3,GETDATE() - 23.4)
insert dbo.OrdersDemo (ID,CustID,ProdID,Qty,OrderTime)
values (NEWID(),1,2,1,GETDATE() - 22.6)
------------------------------------------------------
insert dbo.OrdersDemo (ID,CustID,ProdID,Qty,OrderTime)
values (NEWID(),2,1,1,GETDATE() - 2.4)
insert dbo.OrdersDemo (ID,CustID,ProdID,Qty,OrderTime)
values (NEWID(),2,2,2,GETDATE() - 2.4)
insert dbo.OrdersDemo (ID,CustID,ProdID,Qty,OrderTime)
values (NEWID(),2,3,2,GETDATE() - 24.6)
------------------------------------------------------
insert dbo.OrdersDemo (ID,CustID,ProdID,Qty,OrderTime)
values (NEWID(),3,1,1,GETDATE() - 23.4)
insert dbo.OrdersDemo (ID,CustID,ProdID,Qty,OrderTime)
values (NEWID(),3,1,1,GETDATE() - 1.5)

 

好了，准备工作完毕，进入正题。我们要想办法让订购量最终的报表形式如下：
CustID 产品1 产品2 产品3
1 3 1 0
2 1 2 2
3 2 0 0

 

第一步，检视原始数据。这步其实可以省略，但是我不看到原始数据是没思路的，= =|||
select * from ordersdemo order by custid

 

结果略。

第二步，不管三七二十五。。。还是二十一来着？暂不管原始需求，先考虑每位客户总订购量是多少？
显然SQL语句可以是：
select CustID,SUM(Qty) from OrdersDemo group by CustID

 

第三步，在上面那步的基础上加上考虑分产品的需求。先看产品1的订购量：
select CustID,SUM(case ProdID when 1 then Qty else 0 end) as '产品1' from OrdersDemo group by CustID

这里的case语句用的很精妙。对每条记录，只有当ProdID为1时才统计Qty。如果没有，则取else中的值。

 

有了前三步，第四步就很简单了，补全剩下的产品：
select CustID,
 SUM(case ProdID when 1 then Qty else 0 end) as '产品1',
 SUM(case ProdID when 2 then Qty else 0 end) as '产品2',
 SUM(case ProdID when 3 then Qty else 0 end) as '产品3'  
from OrdersDemo
group by CustID

 

好了，打完收工~