SQL行列转换

来源：互联网发布：穿t恤不好看知乎编辑：程序博客网时间：2024/06/09 23:36

感觉上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

2 0