create table tb(id varchar(10),px int,val int) insert into tb values('a' , 1 , 2) insert into tb values('a' , 2 , 3) insert into tb values('a' , 3 , 3) insert into tb values('b' , 1 , 2) insert into tb values('b' , 2 , 4) insert into tb values('b' , 5 , 5) go-- 如果你每个ID最多三个值。用静态SQL。 select id, max(case px1 when 1 then val else 0 end) val1, max(case px1 when 2 then val else 0 end) val2, max(case px1 when 3 then val else 0 end) val3 from ( select * , px1 = (select count(1) from tb where id = t.id and px < t.px) + 1 from tb t ) m group by id /* id val1 val2 val3 ---------- ----------- ----------- ----------- a 2 3 3 b 2 4 5(所影响的行数为 2 行) */-- 如果你每个ID值的个数不确定。用动态SQL。 declare @sql varchar(8000) set @sql = 'select id ' select @sql = @sql + ' , max(case px1 when ''' + cast(px1 as varchar) + ''' then val else 0 end) [val' + cast(px1 as varchar) + ']' from (select distinct px1 from (select * , px1 = (select count(1) from tb where id = t.id and px < t.px) + 1 from tb t)m) as a set @sql = @sql + ' from (select * , px1 = (select count(1) from tb where id = t.id and px < t.px) + 1 from tb t) m group by id' exec(@sql) /* id val1 val2 val3 ---------- ----------- ----------- ----------- a 2 3 3 b 2 4 5 */drop table tb
If (select object_id('test')) is not null drop table test go create table test( col1 varchar(10), col2 int, col3 int ) insert into test values('A',1,2) insert into test values('A',2,2) insert into test values('A',3,3) insert into test values('B',1,2) insert into test values('B',2,4) insert into test values('B',5,5) insert into test values('C',1,2) insert into test values('C',6,2) insert into test values('C',3,3) go declare @sql varchar(4000),@minChar varchar(10),@max int,@i int select @max=count(distinct col1) from test select @minChar=min(col1) from test select @i=0 while @i<@max begin select @sql='select identity(int,1,1) id,* into ' + '##temp' + char(65+@i) + ' from test ' + 'where col1=''' + char(65+@i) + '''' Exec(@sql) --print @sql select @i=@i+1 select @sql='' end select @i=0 select @sql = 'select * from ' while @i<@max select @sql = @sql + '##temp' + char(65+@i) + ' ' + char(65+@i) + ',',@i=@i+1 select @sql = substring(@sql,1,len(@sql)-1) select @sql = @sql + ' where ' select @i=0 while @i<@max-1 select @sql = @sql + char(65+@i) + '.id = ' + char(66+@i) + '.id and ',@i=@i+1 select @sql = substring(@sql,1,len(@sql)-4) --print @sql exec(@sql) select @i=0 select @sql='' while @i<@max begin select @sql = 'drop table ##temp' + char(65+@i),@i=@i+1 exec(@sql) end
有c 的
a 1 2 b 1 2
a 2 3 b 2 4
a 3 3 b 5 5
c ...............
c ...............
a 1 2 b 1 2 c 1 2 d . .
a 2 3 b 2 4 c . . d . .
a 3 3 b 5 5 c . .
a 2 3 3
b 2 4 5如果这样,就可以使用静(动态)行列转换。
最后select临时表,多表联查。。
insert into tb values('a' , 1 , 2)
insert into tb values('a' , 2 , 3)
insert into tb values('a' , 3 , 3)
insert into tb values('b' , 1 , 2)
insert into tb values('b' , 2 , 4)
insert into tb values('b' , 5 , 5)
go-- 如果你每个ID最多三个值。用静态SQL。
select id,
max(case px1 when 1 then val else 0 end) val1,
max(case px1 when 2 then val else 0 end) val2,
max(case px1 when 3 then val else 0 end) val3
from
(
select * , px1 = (select count(1) from tb where id = t.id and px < t.px) + 1 from tb t
) m
group by id
/*
id val1 val2 val3
---------- ----------- ----------- -----------
a 2 3 3
b 2 4 5(所影响的行数为 2 行)
*/-- 如果你每个ID值的个数不确定。用动态SQL。
declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' , max(case px1 when ''' + cast(px1 as varchar) + ''' then val else 0 end) [val' + cast(px1 as varchar) + ']'
from (select distinct px1 from (select * , px1 = (select count(1) from tb where id = t.id and px < t.px) + 1 from tb t)m) as a
set @sql = @sql + ' from (select * , px1 = (select count(1) from tb where id = t.id and px < t.px) + 1 from tb t) m group by id'
exec(@sql)
/*
id val1 val2 val3
---------- ----------- ----------- -----------
a 2 3 3
b 2 4 5
*/drop table tb
drop table test
go
create table test(
col1 varchar(10),
col2 int,
col3 int
)
insert into test values('A',1,2)
insert into test values('A',2,2)
insert into test values('A',3,3)
insert into test values('B',1,2)
insert into test values('B',2,4)
insert into test values('B',5,5)
insert into test values('C',1,2)
insert into test values('C',6,2)
insert into test values('C',3,3)
go
declare @sql varchar(4000),@minChar varchar(10),@max int,@i int
select @max=count(distinct col1) from test
select @minChar=min(col1) from test
select @i=0
while @i<@max
begin
select @sql='select identity(int,1,1) id,* into ' + '##temp' + char(65+@i) + ' from test ' + 'where col1=''' + char(65+@i) + ''''
Exec(@sql)
--print @sql
select @i=@i+1
select @sql=''
end
select @i=0
select @sql = 'select * from '
while @i<@max
select @sql = @sql + '##temp' + char(65+@i) + ' ' + char(65+@i) + ',',@i=@i+1
select @sql = substring(@sql,1,len(@sql)-1)
select @sql = @sql + ' where '
select @i=0
while @i<@max-1
select @sql = @sql + char(65+@i) + '.id = ' + char(66+@i) + '.id and ',@i=@i+1
select @sql = substring(@sql,1,len(@sql)-4)
--print @sql
exec(@sql)
select @i=0
select @sql=''
while @i<@max
begin
select @sql = 'drop table ##temp' + char(65+@i),@i=@i+1
exec(@sql)
end