[SQL] pivot 转转转

SQL 2005开始可以使用pivot

目标:每天各销售员的成交笔数

要把所有EMP写上去,很麻烦耶,有其它方法,不用写全都列出来吗??(留疑问)

建立销售表

create table #Sale(PurID int,Emp varchar(10),YMD int) Insert into #Saleselect PurID = 1 ,Emp='E01',YMD =20110101Insert into #Saleselect PurID = 2 ,Emp='E01',YMD =20120201Insert into #Saleselect PurID = 5 ,Emp='E01',YMD =20120201Insert into #Saleselect PurID = 3 ,Emp='E02',YMD =20130301Insert into #Saleselect 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 #Salegroup by YMD,Emp

pivot语法

SELECT <column/s> FROM(<source_query>) as srcPIVOT(<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写在这) pPIVOT  (  count( PurID)--算成交笔数的单子写在这里FOR Emp IN  ( [E01], [E02],[E03])  --各销售员写在这里) AS pvt  ORDER BY pvt.YMD; --把固定的写在这