table: test
varchar(2) varchar(2) name value A 01
A 02
A 03
B 01
B 02
B 03
c 01
..............---------------
查询后A 01 02 03
B 01 02 03
c 01......
varchar(2) varchar(2) name value A 01
A 02
A 03
B 01
B 02
B 03
c 01
..............---------------
查询后A 01 02 03
B 01 02 03
c 01......
create table tb(name varchar(10), value varchar(10))
insert into tb values('A', '01')
insert into tb values('A', '02')
insert into tb values('A', '03')
insert into tb values('B', '01')
insert into tb values('B', '02')
insert into tb values('B', '03')
insert into tb values('c', '01')
go--静态SQL,指同一个name最多有3个value
select name ,
max(case px when 1 then value else '' end) value1,
max(case px when 2 then value else '' end) value2,
max(case px when 3 then value else '' end) value3
from
(
select px = (select count(1) from tb where name = t.name and value < t.value) + 1 , * from tb t
) m
group by name
order by name
/*
name value1 value2 value3
---------- ---------- ---------- ----------
A 01 02 03
B 01 02 03
c 01 (3 行受影响)
*/--动态SQL,指同一个name其value个数不定.
declare @sql varchar(8000)
set @sql = 'select Name '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then value else '' '' end) [' + cast(px as varchar) + ']'
from (select distinct px from (select px = (select count(1) from tb where name = t.name and value < t.value) + 1 , * from tb t) m) as a
set @sql = @sql + ' from (select px = (select count(1) from tb where name = t.name and value < t.value) + 1 , * from tb t) m group by name order by name'
exec(@sql)
/*
name value1 value2 value3
---------- ---------- ---------- ----------
A 01 02 03
B 01 02 03
c 01 (3 行受影响)
*/drop table tb
SQL> select zz.tname,
2 max(ltrim(sys_connect_by_path(zz.tvalue,' '),' ')) as path
3 from (
4 select tt.*,
5 tt.tname||(row_number() over(partition by tt.tname order by tt.tvalue)-1) as front_value,
6 tt.tname||row_number() over(partition by tt.tname order by tt.tvalue) as behind_value
7 from test tt
8 )zz
9 connect by prior zz.front_value = zz.behind_value
10 group by zz.tname;TNAME PATH
---------- --------------------------------------------------------------------------------
A 03 02 01
B 03 02 01
C 01