感觉上pivot是可以替代 casewhen的 。例子如下:
CREATE TABLE Good(
color varchar(10),size varchar(2),
remain int)
INSERT INTO Good VALUES('红色','s',10),('红色','M',80),('白色','l',50),('白色','s',60);
SELECT color,
SUM( CASE WHEN size='s 'and remain>0 THEN
remain ELSE 0 END) s,
SUM( CASE WHEN size='m 'and remain>0 THEN
remain ELSE 0 END) m,
SUM( CASE WHEN size='l 'and remain>0 THEN
remain ELSE 0 END) l
FROM Good
GROUP BY color;
select * from Good pivot(sum(remain) for size in(s, m ,l)) a ;
区别只在casewhen 比pivot多设定了一个默认值。
———————————————————————————————————————————————————
如果是动态分组的行列装换的话,那就要麻烦一点了,用存储过程,先distinct获取所有分组,再用pivot进行转换。
下面是不是很好看懂所以可以不看的例子
CREATE PROCEDURE proc_ClassifiedStatistic
AS
BEGIN
DECLARE @PLANTS VARCHAR(500)=''
DECLARE @SQL NVARCHAR(1000)
DECLARE @STime NVARCHAR(1000) ='2015-01-01 00:00:00'
DECLARE @ETime NVARCHAR(1000) ='2015-02-01 00:00:00'
SELECT @PLANTS = @PLANTS + '[' + 付款方式 + '],'
FROM (select distinct [付款方式] from [收费清单表] where [日期] between @STime and @ETime group by [付款方式]
,[门诊] having sum([实收金额])>0) T
SET @PLANTS= LEFT(@PLANTS, LEN(@PLANTS)-1)
SET @SQL=
'SELECT *
FROM (SELECT
sum([实收金额]) 合计
,[付款方式]
,[门诊]
FROM [newdata].[dbo].[收费清单表]
where [日期] between '''+@STime+''' and '''+@ETime+''' group by [付款方式]
,[门诊])X
pivot( sum(合计) FOR [付款方式] IN ({0}))T'
SET @SQL= REPLACE(@SQL,'{0}',@PLANTS)
EXEC sp_executesql @SQL
END