黃昏的甘蔗

 

其實本篇是「多筆資料合併為一筆」的延續，上次說到可以利用「FOR XML PATH('')」及 SubQuery 來串起相關連的資料，並使其列為同一行（ROW），這樣的作法有時是為了串接(cascade)相關連的資料，但更常用於統計週、月、季、年的資料彙整。

本文以 Northwind 範例資料庫為例，說明如何統計每一年度每一個業務員的銷售金額。

使用到顧客「Employees」、訂單「Orders」、訂單明細「Order Details」三個資料表

SELECT [EmployeeID],[LastName] FROM [Northwind].[dbo].[Employees] 

 

SELECT [OrderID],[EmployeeID],[OrderDate] FROM[Northwind].[dbo].[Orders]

SELECT [OrderID],[UnitPrice],[Quantity] FROM [Northwind].[dbo].[Order Details]

-------分隔線 ----------------------------------------------

利用最基本 Join 方式串起3個資料表

 

SELECT e.EmployeeID, e.LastName, convert(varchar(10),o.OrderDate,101) AS orderdate ,(od.Quantity *od.UnitPrice) sale 
FROM dbo.Employees e
inner join orders o on o.EmployeeID = e.EmployeeID
inner join [Order Details] od on od.OrderID = o.OrderID

 

經過這樣的 Join 之後就可以得到銷售員與訂單之間的關聯，並得到該張訂單的金額。所以想要知道某一位銷售員在哪一年的銷售總額是多少，只要再加上簡單的 where 條件跟彙總函數 Sum ，就可以得到。

但是想要一次計算完，就要用一些方法來處理

方法一：利用 Case 欄位 where 條件 then 成立動作 else 不成立動作 end;

 

;
with emp (eid,ename, orderdate,  sale)
as
(
select e.EmployeeID, e.LastName, convert(varchar(10),o.OrderDate,101) as orderdate, (od.Quantity *od.UnitPrice) sale fromdbo.Employees e
inner join orders o on o.EmployeeID = e.EmployeeID
inner join [Order Details] od on od.OrderID = o.OrderID
)
select eid ,ename ,SUM(sale) 'Total',
case YEAR(orderdate) when 1996 then SUM(sale) else 0 end as '1996',
case YEAR(orderdate) when 1997 then SUM(sale) else 0 end as '1997',
case YEAR(orderdate) when 1998 then SUM(sale) else 0 end as '1998'
from emp
group by eid ,ename,YEAR(orderdate)
order by 1

得到下列的結果，顯然不夠好，怎麼會同一個人變成3行呢？

換個方法試試

;
with emp(ename, orderdate,  sale)
as
(
select e.LastName, YEAR(convert(varchar(10),o.OrderDate,101)) as orderdate, (od.Quantity *od.UnitPrice) sale from dbo.Employees e
inner join orders o on o.EmployeeID = e.EmployeeID
inner join [Order Details] od on od.OrderID = o.OrderID
)
select * from emp
pivot
(
sum(sale) for orderdate in ([1996],[1997],[1998])
) as pivot1

ㄝ ～搞定了哩，這不就是我要的「統計每一年度每一個業務員的銷售金額」嗎！

是的！這用了一個 SQL2005 之後提供的功能 PIVOT，他可以提供類似 EXCEL 的轉置函數，將一堆一維的資料轉換成二維的統計結果。

MSDN說明：「LINK」他的格式是

 

SELECT <非樞紐資料行>,
    [第一個樞紐資料行] AS <資料行名稱>,
    [第二個樞紐資料行] AS <資料行名稱>,
    ...
    [最後一個樞紐資料行] AS <資料行名稱>
FROM
    (<產生資料的SELECT 查詢>)
    AS <來源查詢的別名>
PIVOT
(
    <彙總函式>(<要彙總的資料行>)
FOR
[<包含將變成資料行標頭之值的資料行>]
    IN ( [第一個樞紐資料行], [第二個樞紐資料行],
    ... [最後一個樞紐資料行])
) AS <樞紐分析表的別名>
<選擇性的ORDER BY 子句>;

我來用 MSDN 方式重寫一下我上面的 T-SQL，不過格式變動一下

select  *  from
(
select 
e.LastName ,
YEAR(convert(varchar(10),o.OrderDate,101)) as orderdate ,(od.Quantity *od.UnitPrice) sale from dbo.Employees e
inner join orders o on o.EmployeeID = e.EmployeeID
inner join [Order Details] od on od.OrderID = o.OrderID ) as emp
pivot
(
sum(sale) for orderdate in ([1996],[1997],[1998])
) as pivot1

當然你也可以仿照 MSDN 格式寫出你需要的彙總結果。

～  End

QQ：難道真的無法用 Case ... when 來做嗎？
A：當然可以！

 

;
with emp(ename,yr,sale)
as
(
Select e.LastName,YEAR(o.OrderDate)  ,SUM(od.Quantity*od.UnitPrice)
From Employees e
      inner join Orders o on o.EmployeeID = e.EmployeeID 
      inner join [Order Details] od on o.OrderID = od.OrderID 
Group By  e.LastName, YEAR(o.OrderDate)
)
select  ename,
sum(case yr when 1996 then sale else 0 end) as '1996',
sum(case yr when 1997 then sale else 0 end) as '1997',
sum(case yr when 1998 then sale else 0 end) as '1998'
  from emp
Group By ename 

QQ2：或許你會問～為何我無法在 Northwind 執行你的 T-SQL 呢？
AA2：那是因為 Northwind 是很早期的範例資料庫，其Compatibility Level 在你安裝/還原時預設為 2000 ，請把它改為 2005 或 2008 才能使用 PIVOT ，因為這個指令在2005之後才支援。