sql分类汇总、列合并
大家好，下面代码如何实现呢？？？？
[解决办法]
select
联单号,客户
,sum(case when 消费项目 like '%套餐%' then 金额 else 0 end) 套餐
,max(case when 消费项目 like '%套餐%' then
case when 负责人1='无' or ISNULL(负责人1,'')='' then '' else 负责人1 end
+
case when 负责人2='无' or ISNULL(负责人2,'')='' then '' else '、'+负责人2 end
else '' end) 负责人
,sum(case when 消费项目 like '%鲜花%' then 金额 else 0 end) 鲜花
,max(case when 消费项目 like '%鲜花%' then
case when 负责人1='无' or ISNULL(负责人1,'')='' then '' else 负责人1 end
+
case when 负责人2='无' or ISNULL(负责人2,'')='' then '' else '、'+负责人2 end
else '' end) 负责人
,sum(case when 消费项目 like '%化妆%' then 金额 else 0 end) 化妆
,max(case when 消费项目 like '%化妆%' then
case when 负责人1='无' or ISNULL(负责人1,'')='' then '' else 负责人1 end
+
case when 负责人2='无' or ISNULL(负责人2,'')='' then '' else '、'+负责人2 end
else '' end) 负责人
,sum(case when 消费项目 like '%礼服%' then 金额 else 0 end) 礼服
,max(case when 消费项目 like '%礼服%' then
case when 负责人1='无' or ISNULL(负责人1,'')='' then '' else 负责人1 end
+
case when 负责人2='无' or ISNULL(负责人2,'')='' then '' else '、'+负责人2 end
else '' end) 负责人
,SUM(金额) 费用合计
from t
group by 联单号,客户
IF OBJECT_ID('T_CSDN_ONE') IS NOT NULL
BEGIN
DROP TABLE T_CSDN_ONE;
END
GO
CREATE TABLE T_CSDN_ONE
(
DDBH VARCHAR(50),
LDH VARCHAR(20),
KH VARCHAR(50),
XFXM VARCHAR(50),
JE DECIMAL(12,2),
FZRONE VARCHAR(50),
FZRTWO VARCHAR(50)
)
GO
INSERT INTO T_CSDN_ONE
SELECT '2013061301','002673','新人1','3000套餐',3000,'张三','王五'
UNION ALL
SELECT '2013061302','002673','新人1','鲜花',200,'张三',NULL
UNION ALL
SELECT '2013061303','002673','新人1','化妆',600,'赵六',NULL
UNION ALL
SELECT '2013061304','002674','李新人','3999套餐',3999,'李四','王五'
UNION ALL
SELECT '2013061305','002674','李新人','礼服',510,'李四',NULL
GO
SELECT LDH,KH,XFXM,SUM(JE) AS JE,
CASE WHEN MAX(FZRONE) IS NOT NULL THEN ISNULL(MAX(FZRONE),'')
ELSE ''
END
+
CASE WHEN MAX(FZRTWO) IS NOT NULL THEN ','+ISNULL(MAX(FZRTWO),'')
ELSE ''
END AS FZR
INTO ##T_CSDN_ONE
FROM T_CSDN_ONE GROUP BY LDH,KH,XFXM
GO
declare @sql varchar(8000)
set @sql = 'select LDH,KH'
select @sql = @sql + ' ,max(case XFXM when ''' + XFXM + ''' then JE else 0 end) [' + XFXM + '],
max(case XFXM when ''' + XFXM + ''' then FZR else '''' end) [' + XFXM+'责任人]'
from (select distinct XFXM,FZR from ##T_CSDN_ONE) as a
set @sql = @sql + ' INTO ##T_CSDN_TWO from ##T_CSDN_ONE group by LDH,KH ORDER BY LDH'
exec(@sql)
SELECT * FROM ##T_CSDN_TWO
DROP TABLE ##T_CSDN_ONE
DROP TABLE ##T_CSDN_TWO
