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
或是
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

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

pivot語法

SELECT <column/s> FROM
(
	<source_query>
) as src
PIVOT
(
	<aggr_function>	
	(
		<aggr_column>
		FOR <spreading_column> IN (<spreading_elements>)
	)
) as ret

把它轉成這樣

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


圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 則留言

0
神Q超人
iT邦研究生 5 級 ‧ 2018-08-16 18:23:31

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

我要留言

立即登入留言