SELECT BR_NO, DP_NO,
sum(case when SUBSTRING(SL_DATE, 4, 2) ='01' then SL_AMT else 0 end) as [yymm(一月)],
sum(case when SUBSTRING(SL_DATE, 4, 2) ='02' then SL_AMT else 0 end) as [yymm(二月)],
....
FROM CNT_SALE_D
GROUP BY BR_NO, DP_NO
ORDER BY BR_NO, DP_NO
sum(case when SUBSTRING(SL_DATE, 4, 2) ='01' then SL_AMT else 0 end) as [yymm(一月)],
sum(case when SUBSTRING(SL_DATE, 4, 2) ='02' then SL_AMT else 0 end) as [yymm(二月)],
....
FROM CNT_SALE_D
GROUP BY BR_NO, DP_NO
ORDER BY BR_NO, DP_NO
看看这个吧
http://expert.csdn.net/Expert/topic/1815/1815736.xml?temp=.5180475
SET @SQL=''
SELECT @SQL=@SQL+',SUM(CASE WHEN yymm='''+yymm+''' THEN SL_AMT ELSE O END ) AS '+SUBSTRING(SL_DATE, 4, 2) +'月'
from (select distinct SL_DATE from table) aaa
exec (selct BR_NO, DP_NO '+ @sql +' from table where ... group by BR_NO, DP_NO')
caiyunxia(monkey) 的方法简单,扩展性强,但是会漏掉没有数据的月份!
SET @SQL=''
SELECT @SQL=@SQL+',SUM(CASE WHEN SL_DATE like ''___'+rtrim(sl_date)+'__'' THEN sl_amt ELSE 0 END ) [yymm('+rtrim(sl_date)+'月)]'
from (select distinct SUBSTRING(SL_DATE,4,2) sl_date from 有一表) aaa
exec ('select BR_NO,DP_NO'+@sql+' from 有一表 group by BR_NO, DP_NO')
insert #有一表 values('0001','0920101',1 ,'01' , 234.00 ,'01')
insert #有一表 values('0001','0920101', 3 , '03' , 32132.00 ,'03')
insert #有一表 values('0001','0920113', 4 , '01' , 99999999.99 ,'01')
insert #有一表 values('0001','0920301', 2 , '02' ,321321.00 , '03')
insert #有一表 values('0001','0920513', 5, '02', 66666666.66, '03')
insert #有一表 values('0001','0920513', 7 ,'03' , 32132.00 , '01')
insert #有一表 values('0001','0920513', 6 , '05' , 1321.00 , '01')
insert #有一表 values('0001','0920101', 4 , '01' , 32123.00 ,'01')
insert #有一表 values('0002','0910102', 2 , '03' ,11111111.56 , '02')
insert #有一表 values('0002','0910102', 3 , '05' ,24589473.16, '01')
insert #有一表 values('0001','0921105', 2, '01' ,100.00 , '02')
DECLARE @SQL VARCHAR(8000)
SET @SQL=''
SELECT @SQL=@SQL+',SUM(CASE WHEN SL_DATE like ''___'+rtrim(sl_date)+'__'' THEN sl_amt ELSE 0 END ) [yymm('+rtrim(sl_date)+'月)]'
from (select distinct SUBSTRING(SL_DATE,4,2) sl_date from #有一表) aaa
exec ('select BR_NO,DP_NO'+@sql+' from #有一表 group by BR_NO, DP_NO')go
drop table #有一表
insert #有一表 values('0001','0920101',1 ,'01' , 234.00 ,'01')
insert #有一表 values('0001','0920101', 3 , '03' , 32132.00 ,'03')
insert #有一表 values('0001','0920113', 4 , '01' , 99999999.99 ,'01')
insert #有一表 values('0001','0920301', 2 , '02' ,321321.00 , '03')
insert #有一表 values('0001','0920513', 5, '02', 66666666.66, '03')
insert #有一表 values('0001','0920513', 7 ,'03' , 32132.00 , '01')
insert #有一表 values('0001','0920513', 6 , '05' , 1321.00 , '01')
insert #有一表 values('0001','0920101', 4 , '01' , 32123.00 ,'01')
insert #有一表 values('0002','0910102', 2 , '03' ,11111111.56 , '02')
insert #有一表 values('0002','0910102', 3 , '05' ,24589473.16, '01')
insert #有一表 values('0001','0921105', 2, '01' ,100.00 , '02')
DECLARE @SQL VARCHAR(8000)
SET @SQL=''
SELECT @SQL=@SQL+',SUM(CASE WHEN SL_DATE like ''___'+rtrim(sl_date)+'__'' THEN sl_amt ELSE 0 END ) [yymm('+rtrim(sl_date)+'月)]'
from (select '01' sl_date union select '02' union select '03' union select '04' union select '05' union select '06' union select '07' union select '08' union select '09' union select '10' union select '11' union select '12') aaa
exec ('select BR_NO,DP_NO'+@sql+' from #有一表 group by BR_NO, DP_NO')go
drop table #有一表