日期:2014-05-18 浏览次数:20505 次
CREATE TABLE TB (AYEAR INT,AMONTH INT,ATYPE VARCHAR(50),ASUM INT)
INSERT INTO TB SELECT 2008,4,'书款',200
UNION ALL SELECT 2008,3 ,'公车',300
UNION ALL SELECT 2008,5 ,'书款',5
DECLARE @SQL NVARCHAR(4000)
SET @SQL='SELECT ATYPE'
SELECT @SQL=@SQL+N','+QUOTENAME(AMONTH,N'''') +N'=SUM( CASE AMONTH WHEN '+QUOTENAME(AMONTH,N'''')+N'THEN ASUM ELSE 0 END)'
FROM TB
GROUP BY AMONTH
PRINT @SQL
EXEC (@SQL+N' FROM TB GROUP BY ATYPE ')
/*
ATYPE 3 4 5
-------------------------------------------------- ----------- ----------- -----------
公车 300 0 0
书款 0 200 5
*/
DECLARE @SQL NVARCHAR(4000) SELECT @SQL=isnull(@SQL,'')+N',SUM( CASE WHEN AMONTH='''+Convert(nvarchar,AMONTH)+N''' THEN ASUM ELSE 0 END)'+QUOTENAME(Convert(nvarchar,AMONTH)+N'月份') FROM TB GROUP BY AMONTH exec('Select atype'+@sql +' from tb group by atype')