表A:
id,名称,类别,金额
1, aa, 买菜, 30
2, bb, 买书,300
3, aa, 吃饭,100
4、aa, 30天, 1800
5、aa, 40天, 1200行转列:DECLARE @S VARCHAR(8000)
SET @S='SELECT 名称'
SELECT @S=@S+',['+类别+']=CAST(SUM(CASE 类别 WHEN '''+类别+''' THEN 金额 ELSE 0 END) AS NVARCHAR(20))' FROM #FY2 GROUP BY 类别
SET @S=@S+',小计=cast(case when 类别 not like '%天' then sum(金额) else 0 end as nvarchar(50)) FROM #FY2 group by 名称 ORDER BY 名称'
EXEC(@S)--
为什么:SET @S=@S+',小计=cast(case when 类别 not like '%天' then sum(金额) else 0 end as nvarchar(50)) FROM #FY2 group by 名称 ORDER BY 名称'这样不行给啊,请解释以下。
id,名称,类别,金额
1, aa, 买菜, 30
2, bb, 买书,300
3, aa, 吃饭,100
4、aa, 30天, 1800
5、aa, 40天, 1200行转列:DECLARE @S VARCHAR(8000)
SET @S='SELECT 名称'
SELECT @S=@S+',['+类别+']=CAST(SUM(CASE 类别 WHEN '''+类别+''' THEN 金额 ELSE 0 END) AS NVARCHAR(20))' FROM #FY2 GROUP BY 类别
SET @S=@S+',小计=cast(case when 类别 not like '%天' then sum(金额) else 0 end as nvarchar(50)) FROM #FY2 group by 名称 ORDER BY 名称'
EXEC(@S)--
为什么:SET @S=@S+',小计=cast(case when 类别 not like '%天' then sum(金额) else 0 end as nvarchar(50)) FROM #FY2 group by 名称 ORDER BY 名称'这样不行给啊,请解释以下。
--应该这样:
DECLARE @S VARCHAR(8000)
SET @S='SELECT 名称'
SELECT @S=@S+',['+类别+']=SUM(CASE 类别 WHEN '''+类别+''' THEN 金额 ELSE 0 END)' FROM s
GROUP BY 类别
SET @S=@S+',小计=sum(case when 类别 not like ''%天'' then 金额 else 0 end) FROM s group by 名称 ORDER BY 名称'
EXEC(@S)
SET @S=@S+',小计=cast(case when 类别 not like '%天' then sum(金额) else 0 end as nvarchar(50)) FROM #FY2 group by 名称 ORDER BY 名称'--这里有两处错误:
--1、%天应该是两个单引号,
--2、你只以名称分组时,小计不能这样查询```
不过,提示:group by 未包含 类别group by 增加 类别,结果,多了几行无关记录。