行列转换的问题 急!

Question

2个表
表1:
Class_id  Class_name  
1          1班
2          2班
3          3班
....
表2
Stu_id  Stu_name Class_id
101     狗剩1        1
102     狗剩2        1
103     狗剩3        1
...
201     2柱子1       2
202     2柱子2       2
203     2柱子3       2
....
--------------------------------
变成表3
Class_ID      Stu_list
1             狗剩1,狗剩2,狗剩3....
2             2柱子1 ,2柱子2 ,2柱子3 ...
...

Answer 1

写存储过程吧。没有简单的SQL 语句可以搞定

Answer 2

create table t22(Stu_id varchar2(20),Stu_name varchar2(10),Class_id  varchar2(10)); 
insert into t22 values('101','狗剩1','1'); 
insert into t22 values('102','狗剩2','1'); 
insert into t22 values('103','狗剩3','1'); 
insert into t22 values('201','2柱子1','2'); 
insert into t22 values('202','2柱子2 ','2'); 
insert into t22 values('203','2柱子3 ','2'); 
commit;
------------------oracle 9i -------------------------------------------------
SELECT Class_id, LTRIM(MAX(SYS_CONNECT_BY_PATH(Stu_name, ',')), ',') Stu_name 
FROM (SELECT Class_id, 
               Stu_name, 
               MIN(Stu_name) OVER(PARTITION BY Class_id) Stu_name_MIN, 
               (ROW_NUMBER() OVER(ORDER BY Class_id, Stu_name)) + 
               (DENSE_RANK() OVER(ORDER BY Class_id)) NUMID 
          FROM T22) 
START WITH Stu_name = Stu_name_MIN 
CONNECT BY NUMID - 1 = PRIOR NUMID 
GROUP BY Class_id;------------------oracle 10G ---------------------------------
select Class_id,  wmsys.wm_concat(Stu_name) 
from t22 
group by Class_id
------------------执行结果如下---------------------------------
1 1 狗剩1,狗剩2,狗剩3
2 2 2柱子1,2柱子2 ,2柱子3 

Answer 3

http://topic.csdn.net/u/20090422/12/d22bdf3a-eaf4-466a-8e7d-490f94857552.html