还是行转列的问题

wowo05 2010-10-11 11:55:12

一个日期表，日期是不固定的
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

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

...全文

64 点赞收藏 5

写回复

5 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

chuifengde 2010-10-11

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



*/

回复赞

SQLCenter 2010-10-11

--> 测试数据：#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

*/

回复赞

SQLCenter 2010-10-11

--> 测试数据：#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

*/