我有一个table,如下:ID Dept Cell
A01 K1 B1001
A01 K1 B2002
A01 K2 G1001
A01 K2 G2002
A02 K1 B1001
A02 K1 C1001
A02 K1 D1001
.
.
.
我想把它转换成,如下:
ID Dept1 Cell1 Dept2 Cell2 Dept3 Cell3 Dept4 Cell4
A01 K1 B1001 K1 B2002 K2 G1001 K2 G2002
A02 K1 B1001 K1 C1001 K1 D1001同一个ID可以有多个记录,不固定的,希望能够解决,谢谢!
A01 K1 B1001
A01 K1 B2002
A01 K2 G1001
A01 K2 G2002
A02 K1 B1001
A02 K1 C1001
A02 K1 D1001
.
.
.
我想把它转换成,如下:
ID Dept1 Cell1 Dept2 Cell2 Dept3 Cell3 Dept4 Cell4
A01 K1 B1001 K1 B2002 K2 G1001 K2 G2002
A02 K1 B1001 K1 C1001 K1 D1001同一个ID可以有多个记录,不固定的,希望能够解决,谢谢!
drop table tb
gocreate table tb
(
ID varchar(10),
Dept varchar(10),
Cell varchar(10)
)
insert into tb(ID,Dept,Cell) values('A01','K1','B1001')
insert into tb(ID,Dept,Cell) values('A01','K1','B2002')
insert into tb(ID,Dept,Cell) values('A01','K2','G1001')
insert into tb(ID,Dept,Cell) values('A01','K2','G2002')
insert into tb(ID,Dept,Cell) values('A02','K1','B1001')
insert into tb(ID,Dept,Cell) values('A02','K1','C1001')
insert into tb(ID,Dept,Cell) values('A02','K2','D1001')
goif object_id('pubs..test') is not null
drop table test
goselect ID,Dept,Cell , px=(select count(1) from tb where id=a.id and cell<a.cell)+1 into test from tb a
order by id , cell declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ', max(case when px = ''' + rtrim(cast(px as varchar(10))) + ''' then Dept else null end) as Dept' + rtrim(cast(px as varchar(10))) + ',
max(case when px = ''' + rtrim(cast(px as varchar(10))) + ''' then Cell else null end) as Cell' + rtrim(cast(px as varchar(10)))
from test group by px order by px
set @sql = 'select id ' + @sql + ' from test group by id'
exec (@sql)drop table tb
drop table testid Dept1 Cell1 Dept2 Cell2 Dept3 Cell3 Dept4 Cell4
--- ----- ----- ----- ----- ----- ----- ----- -----
A01 K1 B1001 K1 B2002 K2 G1001 K2 G2002
A02 K1 B1001 K1 C1001 K2 D1001 NULL NULL
drop table tb
gocreate table tb
(
ID varchar(10),
Dept varchar(10),
Cell varchar(10)
)
insert into tb(ID,Dept,Cell) values('A01','K1','B1001')
insert into tb(ID,Dept,Cell) values('A01','K1','B2002')
insert into tb(ID,Dept,Cell) values('A01','K2','G1001')
insert into tb(ID,Dept,Cell) values('A01','K2','G2002')
insert into tb(ID,Dept,Cell) values('A02','K1','B1001')
insert into tb(ID,Dept,Cell) values('A02','K1','C1001')
insert into tb(ID,Dept,Cell) values('A02','K2','D1001')
goif object_id('pubs..test') is not null
drop table test
goselect ID,Dept,Cell , px=(select count(1) from tb where id=a.id and cell<a.cell)+1 into test from tb a
order by id , cell declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ', max(case when px = ''' + rtrim(cast(px as varchar(10))) + ''' then Dept else ''' + ''' end) as Dept' + rtrim(cast(px as varchar(10))) + ',
max(case when px = ''' + rtrim(cast(px as varchar(10))) + ''' then Cell else ''' + ''' end) as Cell' + rtrim(cast(px as varchar(10)))
from test group by px order by px
set @sql = 'select id ' + @sql + ' from test group by id'
exec (@sql)drop table tb
drop table test
id Dept1 Cell1 Dept2 Cell2 Dept3 Cell3 Dept4 Cell4
--- ----- ----- ----- ----- ----- ----- ----- -----
A01 K1 B1001 K1 B2002 K2 G1001 K2 G2002
A02 K1 B1001 K1 C1001 K2 D1001
insert into test select 'A01','K1','B1001'
insert into test select 'A01','K1','B2002'
insert into test select 'A01','K2','G1001'
insert into test select 'A01','K2','G2002'
insert into test select 'A02','K1','B1001'
insert into test select 'A02','K1','C1001'
insert into test select 'A02','K1','D1001'
go
declare @sql varchar(8000),@i intselect @sql='',@i=max(num) from (select count(*) as num from test group by ID) twhile @i>0
begin
set @sql= ',Dept'+rtrim(@i)+'=max(case num when '+rtrim(@i)+' then Dept end)'
+ ',Cell'+rtrim(@i)+'=max(case num when '+rtrim(@i)+' then Cell end)'
+ @sql
set @i=@i-1
endset @sql='select ID'+@sql+' from (select t.*,(select count(*) from test where ID=t.ID and (Dept<t.Dept or (Dept=t.Dept and Cell<=t.Cell))) as num from test t) a group by ID'exec(@sql)
drop table test
go
老乌龟的动作海真快阿1
呵呵,冬天了,要冬眠了.
TableRecordID K1 Qty1 K2 Qty2 K3 Qty3 K4 Qty4 K5 Qty5 K6 Qty6 K7 Qty7 K8 Qty8 K9 Qty9
前面所说的那个表就变成
ID Dept Qty
A01 K1 1
A01 K1 2
A01 K1 9
A01 K7 9
A01 K7 11
A02 K5 1
A02 K5 7
A02 K8 3
A02 K8 4得到的结果是:ID K1 Qty1 K2 Qty2 K3 Qty3 K4 Qty4 K5 Qty5 K6 Qty6 K7 Qty7 K8 Qty8 K9 Qty9
A01 K1 12 K7 18
A02 K5 8 K8 7