Originally posted by mike9 You can not do this with a sql query. You have to use PLSQL
Oh no no no, my good man. You'd be amazed what can be done in SQL.
Try this:
Code:
CREATE TABLE CRL_STUDENT_COURSE (STUDENT_ID NUMBER, COURSE_CD CHAR(4));
INSERT INTO CRL_STUDENT_COURSE VALUES(1, 'F101');
INSERT INTO CRL_STUDENT_COURSE VALUES(1, 'D106');
INSERT INTO CRL_STUDENT_COURSE VALUES(2, 'S105');
INSERT INTO CRL_STUDENT_COURSE VALUES(2, 'C101');
INSERT INTO CRL_STUDENT_COURSE VALUES(3, 'C101');
INSERT INTO CRL_STUDENT_COURSE VALUES(3, 'A503');
INSERT INTO CRL_STUDENT_COURSE VALUES(5, 'C102');
SELECT
T.STUDENT_ID,
MAX(DECODE(T.RN, 1, COURSE_CD)) ||
MAX(DECODE(T.RN, 2,', '|| COURSE_CD)) ||
MAX(DECODE(T.RN, 3,', '|| COURSE_CD)) ||
MAX(DECODE(T.RN, 4,', '|| COURSE_CD)) ||
MAX(DECODE(T.RN, 5,', '|| COURSE_CD)) ||
MAX(DECODE(T.RN, 6,', '|| COURSE_CD)) ||
MAX(DECODE(T.RN, 7,', '|| COURSE_CD)) ||
MAX(DECODE(T.RN, 8,', '|| COURSE_CD)) ||
MAX(DECODE(T.RN, 9,', '|| COURSE_CD)) ||
MAX(DECODE(T.RN,10,', '|| COURSE_CD))
AS COURSE_CD_LST
FROM
(
SELECT
CSC.* ,
ROW_NUMBER()
OVER(
PARTITION BY
CSC.STUDENT_ID
ORDER BY
CSC.COURSE_CD
)
AS RN
FROM
CRL_STUDENT_COURSE CSC
) T
GROUP BY
T.STUDENT_ID
The main problem is that the number of courses in the list is arbitrary. However, it really doesn't hurt much if you want to expand the code to handle 500 courses. It'll most likely still be faster than a PL/SQL solution.
Depending on the Oracle release, maybe, actually you can do it in SQL using correlated subqueries to set up the crosstabs view, but this is in SQL-92 and SQL-99, which Oracle doesn't fully support.
Example: Select idno,course,{select course from courses b where b.idno=a.idnot and b.course <> a.course} from courses a;
or something like that.
Joseph R.P. Maloney, CSP,CDP,CCP
'The answer is 42'
You can not do this with a sql query. You have to use PLSQL
Thanx dear for quick reply.
This is a solution of that problem in 9i.
select
ID_NO,
sys.XMLTYPE.getCLOBval(
sys.XMLTYPE.extract(a.xml,'/ROWSET/ROW/COURSE_LIST/text()') ) "Courses"
from
(
select ID_NO, sys.XMLTYPE.createXML( xmlgen.getXML('select ID_NO,
COURSE||'' '' COURSE_LIST from COURSES where ID_NO = '||ID_NO||' order
by ID_NO') ) "XML" from COURSES group by ID_NO
) a
But I wanna handle this with the simple procedure.
Originally posted by jrpm Depending on the Oracle release, maybe, actually you can do it in SQL using correlated subqueries to set up the crosstabs view, but this is in SQL-92 and SQL-99, which Oracle doesn't fully support.
Example: Select idno,course,{select course from courses b where b.idno=a.idnot and b.course <> a.course} from courses a;
or something like that.
I used PL/SQL 8.1.7 to run this query but the results are not as desired. Why?
Reply With Quote
Bookmarks