oracle如何将两张表合成一张,使用Oracle使用PIVOT将三张表合并为一张

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)