又一个SQL的piovt的经典小例子

参考：

http://bbs.csdn.net/topics/310027073

create table test2(id int,name varchar(20),quarter char,profile1 int)
insert into test2 values(1,'a',1,1000)
insert into test2 values(1,'a',2,2000)
insert into test2 values(1,'a',3,4000)
insert into test2 values(1,'a',4,5000)
insert into test2 values(2,'b',1,3000)
insert into test2 values(2,'b',2,3500)
insert into test2 values(2,'b',3,4200)
insert into test2 values(2,'b',4,5500)
select * from test2
select id,name
,[1] as 一季度
,[2] as 二季度
,[3] as 三季度
,[4] as 四季度
from test2
pivot(count([profile1])for [quarter] in ([1],[2],[3],[4])) pvt 

自己写的：

--海尔项目当天抓取数据统计脚本
DECLARE @CUREENT VARCHAR(30)
--抓取总数量
SELECT @CUREENT=CONVERT(varchar(30),getdate(),101)
SELECT GETDATE() as 时间  
  ,count(1)AS 抓取总数量
  FROM [SEC_Item]
  where brand_id=20002 and [Item_CDate]>= @CUREENT
--正常与极速抓取总数量
SELECT [0] AS [正常抓取(总数)],[1]AS [极速抓取(总数)] FROM
(SELECT count(1) as 数量 ,IsFSearchEngine
  FROM [SEC_Item]
  where brand_id=20002 and [Item_CDate]>=@CUREENT group by IsFSearchEngine
) AS A
  pivot(SUM(数量)for IsFSearchEngine in ([0],[1])) pvt 
--详细列表
SELECT [0] AS [正常抓取(数量)]
,[1] AS [极速抓取(数量)]
,CASE Item_State WHEN 0 THEN '未分拣' WHEN 1 THEN '已分拣' WHEN 2 THEN '正在分拣' WHEN 4 THEN '已放弃' END as [分拣情况] 
FROM
(SELECT count(1) as 数量 ,IsFSearchEngine,Item_State
  FROM [SEC_Item]
  where brand_id=20002 and [Item_CDate]>=@CUREENT group by IsFSearchEngine,Item_State 
) 
AS A
  pivot(SUM(数量)for IsFSearchEngine in ([0],[1])) pvt 
GO

日期的行列转换

 

GO
--本周的极速的分拣情况
DECLARE @CUREENT VARCHAR(30)
SELECT @CUREENT='2012-11-12' --周一的日期
DECLARE @dateTable Table ([DATE] datetime )
DECLARE @IsFSearchEngine nvarchar(max)
DECLARE @DATAS nvarchar(max)
DECLARE @ROWS nvarchar(max)
DECLARE @sql nvarchar(max)
DECLARE @num int
SET @IsFSearchEngine='1' --0：非极速、1：极速
SET @NUM=0
SET @sql=''
WHILE @num<5
BEGIN 
INSERT INTO @dateTable values( CONVERT(varchar(11), dateadd(day,@num,@CUREENT),120))
SET @num=@num+1 
END
SELECT @DATAS=ISNULL(@DATAS+',','')+'['+CONVERT(varchar(11),[DATE],120)+']' FROM @dateTable
SELECT @ROWS=ISNULL(@ROWS+',','')+'ISNULL(['+CONVERT(varchar(11),[DATE],120)+'],0) AS ['+CONVERT(varchar(11),[DATE],120)+']' FROM @dateTable
--SELECT @DATAS
--SELECT @ROWS
SET @sql=@sql+'SELECT '
	+@ROWS
	+',CASE Item_State WHEN 0 THEN ''未分拣'' WHEN 1 THEN ''已分拣'' WHEN 2 THEN ''正在分拣'' WHEN 4 THEN ''已放弃'' END as [分拣情况] 
	FROM 
	(
	SELECT COUNT(1)as 数量,CONVERT(varchar(11),[Item_CDate],120) as [DATE],[Item_State]
	FROM [SEC_Item]
	WHERE brand_id=20002 and [Item_CDate]>='+@CUREENT+' and IsFSearchEngine='+@IsFSearchEngine
	+'GROUP BY CONVERT(varchar(11),[Item_CDate],120),[Item_State]
	)
	AS A pivot(SUM(数量)for [DATE] IN ('+@DATAS+')) pvt'
print @sql
EXEC(@sql)