还是行转列的问题

[wowo05]

一个日期表，日期是不固定的
sj（sj）
2010-08
2010-09
2010-10

一个奖金数表
id sj yf(应发) sf（实发）
001 2010 -10 100 0
001 2010 -09 100 -20
001 2010 -08 100 -100

002 2010 -10 200 -100
002 2010 -08 200 -100
002 2010 -07 200 -200

003 2010 -10 300 -100
003 2010 -09 300 -300
003 2010 -08 300 -300


想达到根据时间表中的时间，来转行奖金数
id 2010-08 2010-08 2010-09 2010-09 2010-10 2010-10 sum
001 100 -100 100 -20 100 0 180
002 200 -100 0 0 200 -100 200
003 300 -300 300 -300 300 -100 200


没有包含在日期表的不统计

[webchanger]

什么问题呢？没明白问题

[wowo05]

根据前面2个表，得出一个统计表出来

[SQLCenter]

--> 测试数据：#1
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1(sj varchar(8))
insert into #1
select '2010-08' union all
select '2010-09' union all
select '2010-10'
--> 测试数据：#2
if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2(id varchar(8), sj varchar(8), yf int, sf int)
insert into #2
select '001', '2010-10', 100, 0 union all
select '001', '2010-09', 100, -20 union all
select '001', '2010-08', 100, -100 union all
select '002', '2010-10', 200, -100 union all
select '002', '2010-08', 200, -100 union all
select '002', '2010-07', 200, -200 union all
select '003', '2010-10', 300, -100 union all
select '003', '2010-09', 300, -300 union all
select '003', '2010-08', 300, -300

declare @sql varchar(8000)
select @sql = isnull(@sql+',','')
	+ '['+sj+'.yf]=sum(case when sj='''+sj+''' then yf else 0 end),'
	+ '['+sj+'.sf]=sum(case when sj='''+sj+''' then sf else 0 end)'
from #1

exec ('select id,'+@sql+' from #2 group by id')

/*
id       2010-08.yf  2010-08.sf  2010-09.yf  2010-09.sf  2010-10.yf  2010-10.sf
-------- ----------- ----------- ----------- ----------- ----------- -----------
001      100         -100        100         -20         100         0
002      200         -100        0           0           200         -100
003      300         -300        300         -300        300         -100
*/

[SQLCenter]

--> 测试数据：#1
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1(sj varchar(8))
insert into #1
select '2010-08' union all
select '2010-09' union all
select '2010-10'
--> 测试数据：#2
if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2(id varchar(8), sj varchar(8), yf int, sf int)
insert into #2
select '001', '2010-10', 100, 0 union all
select '001', '2010-09', 100, -20 union all
select '001', '2010-08', 100, -100 union all
select '002', '2010-10', 200, -100 union all
select '002', '2010-08', 200, -100 union all
select '002', '2010-07', 200, -200 union all
select '003', '2010-10', 300, -100 union all
select '003', '2010-09', 300, -300 union all
select '003', '2010-08', 300, -300

declare @sql varchar(8000)
select @sql = isnull(@sql+',','')
	+ '['+sj+'.yf]=sum(case when sj='''+sj+''' then yf else 0 end),'
	+ '['+sj+'.sf]=sum(case when sj='''+sj+''' then sf else 0 end)'
from #1

exec ('select id,'+@sql+', [sum]=sum(yf+sf) from #2 where sj in (select sj from #1) group by id')

/*
id       2010-08.yf  2010-08.sf  2010-09.yf  2010-09.sf  2010-10.yf  2010-10.sf  sum
-------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
001      100         -100        100         -20         100         0           180
002      200         -100        0           0           200         -100        200
003      300         -300        300         -300        300         -100        200
*/

[chuifengde]

CREATE TABLE #x1(sj VARCHAR(20))
INSERT #x1 SELECT '2010-08'
union all select '2010-09'
union all select '2010-10'

CREATE TABLE #x2(id CHAR(3),sj VARCHAR(20), yf INT, sf INT)
INSERT #x2 SELECT  '001', '2010-10', 100, 0
union all select '001', '2010-09', 100, -20
union all select '001', '2010-08', 100, -100
union all select '002', '2010-10', 200, -100
union all select '002', '2010-08', 200, -100
union all select '002', '2010-07', 200, -200
union all select '003', '2010-10', 300, -100
union all select '003', '2010-09', 300, -300
union all select '003', '2010-08', 300, -300


DECLARE @sql VARCHAR(8000)
SELECT @sql =ISNULL(@sql+',','')+' sum(case when a.sj='''+sj+''' then yf else 0 end) ['+sj+'(yf)],
sum(case when a.sj='''+sj+''' then sf else 0 end) ['+sj+'(sf)]'
FROM #x1 
EXEC('select id,'+@sql+',sum(yf+sf) [sum] from #x1 a inner join #x2 b on a.sj=b.sj group by id')

DROP TABLE #x1,#x2
--result
/*id   2010-08(yf) 2010-08(sf) 2010-09(yf) 2010-09(sf) 2010-10(yf) 2010-10(sf) sum         
---- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
001  100         -100        100         -20         100         0           180
002  200         -100        0           0           200         -100        200
003  300         -300        300         -300        300         -100        200

*/