求一条sql

Question

select  b.teacher, decode(rn,1,student) student1, decode(rn,2,student)student2 ,decode(rn,2,student) student3
from 
(select teacher,student,row_number()over(partition by teacher order by  student)
from a
) 
这样以后再用字符串分组后拼接就可以了,用SYS_CONNECT_BY_PATH.麻烦了点,所以我也不想写了.看别人有什么好思路吧!

Answer 1

select teacher, decode(rn,1,student) student1, decode(rn,2,student)student2 ,decode(rn,2,student) student3
from 
(select teacher,student,row_number()over(partition by teacher order by  student)
from a
) 
上面的SQL多了个B.

Answer 2

select teacher, decode(rn,1,student) student1, decode(rn,2,student)student2 ,decode(rn,2,student) student3
from 
(select teacher,student,row_number()over(partition by teacher order by  student) as rn
from a
) 
这个SQL才行吧,我老是写错.

Answer 3

select teacher, max(decode(rn,1,student)) student1, max(decode(rn,2,student)) student2 ,max(decode(rn,3,student)) student3
from 
(select teacher,student,row_number()over(partition by teacher order by  student) as rn
from TS
) 
GROUP BY teacher
/

Answer 4

运行结果为：
SQL> /TEACHER  STUDENT1 STUDENT2 STUDENT3
-------- -------- -------- --------
TEA1     STU1     STU2     STU3
TEA2     STU4     STU5     STU6

Answer 5

对,今天刚想到不用做字符串连接,可以用MAX!

Answer 6

CREATE TABLE TS(
TEACHER VARCHAR2(30),
STUDENT VARCHAR2(30))INSERT INTO TS VALUES('T1','S1');INSERT INTO TS VALUES('T1','S2');INSERT INTO TS VALUES('T1','S3');select teacher, max(decode(rn,1,student)) student1, max(decode(rn,2,student)) student2 ,max(decode(rn,3,student)) student3
from 
(select teacher,student,row_number()over(partition by teacher order by  student) as rn
from TS
) 
GROUP BY teacherINSERT INTO TS VALUES('T2','S1');INSERT INTO TS VALUES('T2','S2');INSERT INTO TS VALUES('T2','S3');SELECT * FROM TS