-
select ID_NO, COURSE from COURSES
order by ID_NO
ID_NO Course
--------------
1 F101
1 D106
2 S105
2 C101
3 C101
3 A503
5 C102
I wanna see the result in below format through simple procedure, Help.
ID_NO Courses
-----------------
1 F101 D106
2 S105 C101
3 C101 A503
5 C102
-
You can not do this with a sql query. You have to use PLSQL
-
go to asktom.oracle.com and search for pivot query
-
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.
- Chris
-
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.
-
-
Query
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|