表A:
deptid partid dates qty re
A001 00001 2007/08/01 500 GGGG
A001 00001 2007/08/02 1000 HHHH
.
.
.
想得到表B:
deptid partid 2007/08/01 re 2007/08/02 re
A001 00001 500 GGGG 1000 HHHH
需要如何解决?急!
deptid partid dates qty re
A001 00001 2007/08/01 500 GGGG
A001 00001 2007/08/02 1000 HHHH
.
.
.
想得到表B:
deptid partid 2007/08/01 re 2007/08/02 re
A001 00001 500 GGGG 1000 HHHH
需要如何解决?急!
insert into tb values('A001','00001','2007/08/01',500 ,'GGGG')
insert into tb values('A001','00001','2007/08/02',1000,'HHHH')
godeclare @sql varchar(8000)
set @sql = 'select deptid,partid'
select @sql = @sql + ' , max(case dates when ''' + convert(varchar(10),dates,120) + ''' then qty else 0 end) [' + convert(varchar(10),dates,120) + ']'
+ ' , max(case dates when ''' + convert(varchar(10),dates,120) + ''' then re else '' '' end) [re]'
from (select distinct convert(varchar(10),dates,120) dates from tb) as a
set @sql = @sql + ' from tb group by deptid,partid'
exec(@sql)
drop table tb/*
deptid partid 2007-08-01 re 2007-08-02 re
---------- ---------- ----------- ---------- ----------- ----------
A001 00001 500 GGGG 1000 HHHH
*/
insert into tb values('A001','00001','2007/08/01',500 ,'GGGG')
insert into tb values('A001','00001','2007/08/02',1000,'HHHH')
goselect deptid,partid,
max(case when dates = '2007-08-01' then qty end) '2007-08-1',
max(case when dates = '2007-08-01' then re end) 're',
max(case when dates = '2007-08-02' then qty end) '2007-08-2',
max(case when dates = '2007-08-02' then re end) 're'
from tb
group by deptid,partid
drop table tb/*
deptid partid 2007-08-1 re 2007-08-2 re
---------- ---------- ----------- ---------- ----------- ----------
A001 00001 500 GGGG 1000 HHHH(所影响的行数为 1 行)
*/