目標:每天各銷售員的成交筆數
要把所有EMP寫上去,很麻煩耶,有其它方法,不用寫全都列出來嗎??(留疑問)
建立銷售表
create table #Sale
(
PurID int
,Emp varchar(10)
,YMD int
)
Insert into #Sale select PurID = 1 ,Emp=’E01′,YMD =20110101
Insert into #Sale select PurID = 2 ,Emp=’E01′,YMD =20120201
Insert into #Sale select PurID = 5 ,Emp=’E01′,YMD =20120201
Insert into #Sale select PurID = 3 ,Emp=’E02′,YMD =20130301
Insert into #Sale select PurID = 4 ,Emp=’E03′,YMD =20180401
或是
Insert into #Sale (PurID,Emp,YMD) values
(1 ,’E01′,20110101),
(2 ,’E01′,20120201),
(5 ,’E01′,20120201),
(3 ,’E02′,20130301),
(4 ,’E03′,20180401)
本來是這樣
select YMD,Emp, count(*) from #Sale
group by YMD,Emp
SELECT YMD , [E01] AS Emp1, [E02] AS Emp2, [E03] AS Emp2–各銷售員寫在這裡
FROM
(
SELECT PurID, Emp, YMD –把各欄位寫在這
FROM #Sale –把要撈的table寫在這
) p
PIVOT
(
count( PurID) –算成交筆數的單子寫在這裡
FOR Emp IN
( [E01], [E02],[E03]) –各銷售員寫在這裡
) AS pvt
ORDER BY pvt.YMD; –把固定的寫在這
转载请注明：XAMPP中文组官网 » [SQL] pivot 轉轉轉