declare @t table( id varchar(3), type varchar(10), [money] int)insert into @t select '001', '奖金', 10 union all select '001', '补贴', 10 union all select '002', '提成', 10 union all select '002', '补扣', -10select distinct v.id '编号', Isnull(v1.[money], 0) '奖金', isnull(v2.[money], 0) '补贴', isnull(v3.[money], 0) '提成', isnull(v4.[money], 0) '补扣' from @t v left join (select * from @t where type='奖金') v1 on v.id=v1.id left join (select * from @t where type='补贴') v2 on v2.id=v1.id left join (select * from @t where type='提成') v3 on v3.id=v1.id left join (select * from @t where type='补扣') v4 on v.id=v4.id
复制上面所有的SQL在查询分器中执行.
create table t1 (id varchar(10),type varchar(10),money int) insert t1 select '001','奖金',10 union all select '001','补贴',10 union all select '002','提程',10 union all select '002','扣补',-10 --如果TYPE固定 Select 编号=id, SUM(Case type When '奖金' Then money Else 0 End) As 奖金, SUM(Case type When '补贴' Then money Else 0 End) As 补贴, SUM(Case type When '提程' Then money Else 0 End) As 提程, SUM(Case type When '扣补' Then money Else 0 End) As 扣补 From t1 Group By id Order By id--如果TYPE不固定 Declare @S Varchar(1000) Set @S='' Select @S=@S+',SUM(Case TYPE When '''+TYPE+''' Then money Else 0 End) As '+TYPE From (Select Distinct TYPE From t1) A Order By TYPE Set @S='Select id'+@S+' From t1 Group By id Order By id' EXEC(@S)
不知你用的什么数据库,如果是sqlserver/oracle9i/mysql:可用: create table test1(id varchar(3),type varchar(4),money integer); ---------------------------------------------------------------- insert into test1 values('001', '奖金', 10); insert into test1 values('001', '补贴', 10); insert into test1 values('002', '提程', 10); insert into test1 values('002', '扣补',-10); ---------------------------------------------------- select id as '编号',sum(jj) as '奖金' ,sum(bt) as '补贴',sum(tc) as '提程',sum(kb) as '扣补' from ( select id , case type when '奖金' then money else 0 end as jj, case type when '补贴' then money else 0 end as bt, case type when '提程' then money else 0 end as tc, case type when '扣补' then money else 0 end as kb from test1 a ) b group by id --------------------------------------------------------------------- 结果:+------+------+------+------+------+ | 编号 | 奖金 | 补贴 | 提程 | 扣补 | +------+------+------+------+------+ | 001 | 10 | 10 | 0 | 0 | | 002 | 0 | 0 | 10 | -10 | +------+------+------+------+------+ 2 rows in set (0.00 sec)如果你用access/foxpro,改case...when为iif 如果你用oracle8i以前版本,改case...when为decode 参考我以前回的 http://community.csdn.net/Expert/topic/4288/4288492.xml?temp=.9017908
id varchar(3),
type varchar(10),
[money] int)insert into @t
select '001', '奖金', 10
union all select '001', '补贴', 10
union all select '002', '提成', 10
union all select '002', '补扣', -10select distinct v.id '编号', Isnull(v1.[money], 0) '奖金', isnull(v2.[money], 0) '补贴',
isnull(v3.[money], 0) '提成', isnull(v4.[money], 0) '补扣'
from @t v
left join (select * from @t where type='奖金') v1
on v.id=v1.id
left join (select * from @t where type='补贴') v2
on v2.id=v1.id
left join (select * from @t where type='提成') v3
on v3.id=v1.id
left join (select * from @t where type='补扣') v4
on v.id=v4.id
insert t1 select '001','奖金',10
union all select '001','补贴',10
union all select '002','提程',10
union all select '002','扣补',-10
--如果TYPE固定
Select
编号=id,
SUM(Case type When '奖金' Then money Else 0 End) As 奖金,
SUM(Case type When '补贴' Then money Else 0 End) As 补贴,
SUM(Case type When '提程' Then money Else 0 End) As 提程,
SUM(Case type When '扣补' Then money Else 0 End) As 扣补
From t1
Group By id
Order By id--如果TYPE不固定
Declare @S Varchar(1000)
Set @S=''
Select @S=@S+',SUM(Case TYPE When '''+TYPE+''' Then money Else 0 End) As '+TYPE From (Select Distinct TYPE From t1) A Order By TYPE
Set @S='Select id'+@S+' From t1 Group By id Order By id'
EXEC(@S)
create table test1(id varchar(3),type varchar(4),money integer);
----------------------------------------------------------------
insert into test1 values('001', '奖金', 10);
insert into test1 values('001', '补贴', 10);
insert into test1 values('002', '提程', 10);
insert into test1 values('002', '扣补',-10);
----------------------------------------------------
select id as '编号',sum(jj) as '奖金' ,sum(bt) as '补贴',sum(tc) as '提程',sum(kb) as '扣补'
from
(
select id ,
case type when '奖金' then money else 0 end as jj,
case type when '补贴' then money else 0 end as bt,
case type when '提程' then money else 0 end as tc,
case type when '扣补' then money else 0 end as kb
from test1 a
) b
group by id
---------------------------------------------------------------------
结果:+------+------+------+------+------+
| 编号 | 奖金 | 补贴 | 提程 | 扣补 |
+------+------+------+------+------+
| 001 | 10 | 10 | 0 | 0 |
| 002 | 0 | 0 | 10 | -10 |
+------+------+------+------+------+
2 rows in set (0.00 sec)如果你用access/foxpro,改case...when为iif
如果你用oracle8i以前版本,改case...when为decode
参考我以前回的
http://community.csdn.net/Expert/topic/4288/4288492.xml?temp=.9017908