iT邦幫忙

4

[SQL] pivot 轉轉轉

SQL 2005開始可以使用pivot

目標:每天各銷售員的成交筆數

要把所有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

本來是這樣

select YMD,Emp, count(*) from #Sale
group by YMD,Emp

https://ithelp.ithome.com.tw/upload/images/20180816/201067649yoNgflmcH.png

把它轉成這樣

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; --把固定的寫在這

https://ithelp.ithome.com.tw/upload/images/20180816/20106764BZcYt6bILK.png


1 則留言

0
神Q超人
iT邦新手 3 級 ‧ 2018-08-16 18:23:31

推一個樞紐分析查詢,
真的很方便/images/emoticon/emoticon12.gif

我要留言

立即登入留言