22,209
社区成员
发帖
与我相关
我的任务
分享
--动态
DECLARE @SQL VARCHAR(MAX),@COLUMN VARCHAR(MAX)
SET @SQL=''
SET @COLUMN=''
SELECT @SQL=@SQL+',['+name+'],['+name+'滞纳金]'
,@COLUMN=@COLUMN+'+ISNULL(['+name+'],0)+ISNULL(['+name+'滞纳金],0)'
FROM TB GROUP BY name
SET @SQL='SELECT *,'+STUFF(@COLUMN,1,1,'')+'[合计] FROM(SELECT roomid,name,费用 FROM TB UNION ALL SELECT roomid,name+''滞纳金'',滞纳金 FROM TB)T PIVOT(SUM(费用)FOR name IN('
+STUFF(@SQL,1,1,'')+'))P'
--PRINT @SQL
EXEC(@SQL)
我一直觉得PIVOT不如CASE 方便--动态
DECLARE @SQL VARCHAR(MAX)--SQL2005+可以用VARCHAR(MAX)代替
SET @SQL=''
SELECT @SQL=@SQL+',['+name+']'+',['+name+'滞纳金]'
FROM TB GROUP BY name
SET @SQL='SELECT * FROM(SELECT roomid,name,费用 FROM TB UNION ALL SELECT roomid,name+''滞纳金'',滞纳金 FROM TB)T PIVOT(SUM(费用)FOR name IN('
+STUFF(@SQL,1,1,'')+'))P'
PRINT @SQL
EXEC(@SQL)