要显示成如下格式:
碧 康 央
-----------------------------------------------------
月 05数量 06数量 同比 05数量 06数量 同比 05数量 06数量 同比
3 NULL NULL 0 NULL NULL 0 300 0 0
4 970 0 0 2044 0 0 3010 0 0
5 NULL NULl 0 NULL NULL 0 2716 450 -83
----------------------------------------------------------------------------------
合计 970 0 2044 0 6026 450
碧 康 央
-----------------------------------------------------
月 05数量 06数量 同比 05数量 06数量 同比 05数量 06数量 同比
3 NULL NULL 0 NULL NULL 0 300 0 0
4 970 0 0 2044 0 0 3010 0 0
5 NULL NULl 0 NULL NULL 0 2716 450 -83
----------------------------------------------------------------------------------
合计 970 0 2044 0 6026 450
SUM(case when 名称='康' then xl05 else 0 end) as '康05',SUM(case when 名称='康' then xl06 else 0 end) as '康06' ,SUM(case when 名称='康' then 同比 else 0 end) as '康同比' ,
SUM(case when 名称='央' then xl05 else 0 end) as '央05',SUM(case when 名称='央' then xl06 else 0 end) as '央06',SUM(case when 名称='央' then 同比 else 0 end) as '央同比'
FROM (SELECT dd.*,(case when xl06<>0 then (1-xl05/xl06)*100 else 0000.0000 end) as 同比 from (SELECT 月,名称,SUM(case when 年='2005' then 数量 else 0 end) as xl05,
SUM(case when 年='2006' then 数量 else 0 end) as xl06 FROM qqwE GROUP BY 月,名称) dd) dd1 GROUP BY 月 UNION ALL
SELECT '合计' as 月,SUM(碧05) as 碧05,SUM(碧06) as 碧05 ,0 as 碧同比,
SUM(康05) as 康05,SUM(康06) as 康05 ,0 as 康同比,
SUM(央05) as 央05,SUM(央06) as 央05 ,0 as 央同比
FROM (SELECT 月,SUM(case when 名称='碧' then xl05 else 0 end ) as 碧05,SUM(case when 名称='碧' then xl06 else 0 end) as 碧06 ,SUM( case when 名称='碧' then 同比 else 0 end ) as '碧同比' ,
SUM(case when 名称='康' then xl05 else 0 end) as '康05',SUM(case when 名称='康' then xl06 else 0 end) as '康06' ,SUM(case when 名称='康' then 同比 else 0 end) as '康同比' ,
SUM(case when 名称='央' then xl05 else 0 end) as '央05',SUM(case when 名称='央' then xl06 else 0 end) as '央06',SUM(case when 名称='央' then 同比 else 0 end) as '央同比'
FROM (SELECT dd.*,(case when xl06<>0 then (1-xl05/xl06)*100 else 0000.0000 end) as 同比 from (SELECT 月,名称,SUM(case when 年='2005' then 数量 else 0 end) as xl05,
SUM(case when 年='2006' then 数量 else 0 end) as xl06 FROM qqwE GROUP BY 月,名称) dd) dd1 GROUP BY 月)DD2
insert into #t select 1,'碧',970 ,2005,4
insert into #t select 2,'康',2044,2005,4
insert into #t select 3,'央',3010,2005,4
insert into #t select 4,'央',2716,2005,5
insert into #t select 5,'央',300 ,2006,3
insert into #t select 6,'央',450 ,2006,5
DECLARE @S VARCHAR(8000),@V VARCHAR(4000),@NAME VARCHAR(10)
SET @S = ''
SET @V = ''
SELECT @S = @S+',['+名称+'05数量]=SUM(CASE WHEN 年=2005 AND 名称='''+名称+''' THEN 数量 END)'
+',['+名称+'06数量]=SUM(CASE WHEN 年=2006 AND 名称='''+名称+''' THEN 数量 END)'
+',['+名称+'同比]=CAST(ISNULL((1-(SUM(CASE WHEN 年=2005 AND 名称='''+名称+''' THEN 数量 END)+0.0)/SUM(CASE WHEN 年=2006 AND 名称='''+名称+''' THEN 数量 END))*100,0) AS INT)',
@V = @V+',['+名称+'05数量]=SUM(CASE WHEN 年=2005 AND 名称='''+名称+''' THEN 数量 END)'
+',['+名称+'06数量]=SUM(CASE WHEN 年=2006 AND 名称='''+名称+''' THEN 数量 END)'
+',NULL'
FROM (SELECT DISTINCT 名称 FROM #T) ASET @S = 'SELECT 月=RTRIM(月)'+@S+' FROM #t GROUP BY 月 UNION SELECT ''合计'''+@V+' FROM #T'EXEC(@S)