SQL 2005開始可以使用pivot

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

要把所有EMP寫上去,很麻煩耶,有其它方法,不用寫全都列出來嗎??(留疑問)

建立銷售表

drop table #Sale
create table #Sale
(
	PurID int
	,Emp varchar(10)
	,YMD int
) 
Insert into #Sale	select PurID = 1 ,Emp='林懷民',YMD =20110101
Insert into #Sale	select PurID = 2 ,Emp='林懷民',YMD =20120201
Insert into #Sale	select PurID = 5 ,Emp='林懷民',YMD =20120201
Insert into #Sale	select PurID = 3 ,Emp='陳念真',YMD =20130301
Insert into #Sale	select PurID = 4 ,Emp='柯一正',YMD =20180401

或是
Insert into #Sale	(PurID,Emp,YMD) values 
(1 ,'林懷民',20110101),
(2 ,'林懷民',20120201),
(5 ,'林懷民',20120201),
(3 ,'陳念真',20130301),
(4 ,'柯一正',20180401)

本來是這樣

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

pivot語法

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

把它轉成這樣

 SELECT YMD , [林懷民] , [陳念真] ,[柯一正] --各銷售員寫在這裡
FROM   
(
	SELECT PurID, Emp, YMD	--把各欄位寫在這
	FROM #Sale				--把要撈的table寫在這
) p	
PIVOT  
(  
	count( PurID)	--算成交筆數的單子寫在這裡
	FOR Emp IN  
		( [林懷民] , [陳念真] ,[柯一正] )  --各銷售員寫在這裡
) AS pvt  
ORDER BY pvt.YMD; --把固定的寫在這