I have three Oracle SQL select queries which returns following results.
First select query returns result:
user_id | user_name |
---------|-----------|
1 | user_1 |
2 | user_2 |
3 | user_3 |
4 | user_4 |
second select query returns result:
exam_id | exam_name |
---------|-----------|
1 | exam_1 |
2 | exam_2 |
3 | exam_3 |
and the third select query returns result:
exam_id | user_id | exam_date |
---------|---------|-----------|
1 | 1 | 2017 |
1 | 2 | 2018 |
1 | 3 | 2017 |
2 | 3 | 2018 |
I would like to combine these queries to get result:
user_id | user_name | exam_1 | exam_2 | exam_3 |
---------|-----------|--------|--------|--------|
1 | user_1 | 2017 | | |
2 | user_2 | 2018 | | |
3 | user_3 | 2017 | 2018 | |
4 | user_4 | | | |
I would be grateful for any help?
Thank you @shrek for helping me out here. I managed to create the variable for pivot values but couldn't put the variable in the pivot. So I got help for that here and the final version (for 11g) looks like this:
variable x REFCURSOR
DECLARE
exam_ids VARCHAR2(255);
BEGIN
SELECT
LISTAGG(''''
|| exam_id
|| ''' AS "'
|| exam_name
|| '"',',') WITHIN GROUP(
ORDER BY
exam_id ASC
)
INTO exam_ids
FROM
exam;
OPEN :x FOR 'SELECT
*
FROM
(
SELECT
u.user_id,
u.user_name,
e.exam_id,
eu.exam_date
FROM
users u
LEFT JOIN exam_user eu ON u.user_id = eu.user_id
LEFT JOIN exam e ON e.exam_id = eu.exam_id
ORDER BY
u.user_id
)
PIVOT ( MAX ( exam_date )
FOR exam_id
IN ( ' || EXAM_IDS || ' )
)
ORDER BY
1';
END;
/
print x
So that works in the SQL Developer and SQL*Plus. But not when trying to use database with PHP file. For that I needed to create procedure which then could be called from PHP file. Here is problem when trying to use code above from PHP file and the resolution.
解决方案
This should get you going -
CREATE TABLE users
(user_id varchar2(9), user_name varchar2(11))
;
INSERT ALL
INTO users (user_id, user_name)
VALUES ('1', 'user_1')
INTO users (user_id, user_name)
VALUES ('2', 'user_2')
INTO users (user_id, user_name)
VALUES ('3', 'user_3')
INTO users (user_id, user_name)
VALUES ('4', 'user_4')
SELECT * FROM dual
;
CREATE TABLE exam
(exam_id varchar2(9), exam_name varchar2(11))
;
INSERT ALL
INTO exam (exam_id, exam_name)
VALUES ('1', 'exam_1')
INTO exam (exam_id, exam_name)
VALUES ('2', 'exam_2')
INTO exam (exam_id, exam_name)
VALUES ('3', 'exam_3')
SELECT * FROM dual
;
CREATE TABLE exam_user
(exam_id varchar2(9), user_id varchar2(9), exam_date varchar2(11))
;
INSERT ALL
INTO exam_user (exam_id, user_id, exam_date)
VALUES ('1', '1', '2017')
INTO exam_user (exam_id, user_id, exam_date)
VALUES ('1', '2', '2018')
INTO exam_user (exam_id, user_id, exam_date)
VALUES ('1', '3', '2017')
INTO exam_user (exam_id, user_id, exam_date)
VALUES ('2', '3', '2018')
SELECT * FROM dual
;
Query -
SELECT * FROM (
SELECT U.USER_ID, U.USER_NAME, E.EXAM_NAME,EU.EXAM_DATE
FROM USERS U, EXAM E, EXAM_USER EU
WHERE U.USER_ID = EU.USER_ID(+)
AND E.EXAM_ID(+) = EU.EXAM_ID
ORDER BY U.USER_ID
)
PIVOT (MAX(EXAM_DATE) FOR EXAM_NAME IN ('exam_1' as exam_1, 'exam_2' as exam_2,'exam_3' as exam_3))
order by 1
;
Output -
USER_ID USER_NAME EXAM_1 EXAM_2 EXAM_3
1 user_1 2017 (null) (null)
2 user_2 2018 (null) (null)
3 user_3 2017 2018 (null)
4 user_4 (null) (null) (null)