一个sql语句——使列数据转换为行数据

今天被人问及一个问题，大致如下：

假设一张表 JOB，数据是这样的

NAME JOB

1 A

1 B

2 A

2 B

想通过编写sql，使最后达到这样的效果

NAME JOB

1 AB

2 AB

第一反应是最好建立一个中间表，通过编写存储过程使之最后出现这样的结果，大致代码如下：

declare
name1 varchar2(50);
name2 varchar2(50);
job char(500);
job1 varchar2(50);
cursor darmee_c is
select distinct darmee.name,job.job from darmee,job where darmee.name = job.name;
begin
open darmee_c;
loop
fetch darmee_c into name2,job1;
exit when sql%notfound;
if (name2 <> name1 and name1 is null)
then job := job1;
name1 := name2;
elsif (name2 = name1)
then name1 := name2;
job := job||job1;
else
insert into jobtest values (name1,job);
name1 := name2;
job := job1;
end if;
end loop;
insert into jobtest values (name1,job);
close darmee_c;
end;

后来又被问到是否能够只使用sql语句来解决?!

苦思冥想之，最后只得到一个折衷的办法，这个办法的局限就是要事先知道源表内某个name的最大job数，假如其中的一个name的job数为5，则sql语句如下：

select distinct job1.name,(select job from (select name,job,row_number()over(partition by name order by job) id from job) job2
where job2.name = job1.name and job2.id = 1)||
(select job from (select name,job,row_number()over(partition by name order by job) id from job) job2
where job2.name = job1.name and job2.id = 2)||
(select job from (select name,job,row_number()over(partition by name order by job) id from job) job2
where job2.name = job1.name and job2.id = 3)||
(select job from (select name,job,row_number()over(partition by name order by job) id from job) job2
where job2.name = job1.name and job2.id = 4)||
(select job from (select name,job,row_number()over(partition by name order by job) id from job) job2
where job2.name = job1.name and job2.id = 5)
from job job1

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/789181/viewspace-1001134/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/789181/viewspace-1001134/