Я пытаюсь использовать listagg как listagg(select...), но я думаю, что это невозможно так, как я хотел бы сказать.
У меня есть таблица степеней.
Degree table:
-----------------------------------------------
| user_id | degree_fi | degree_en | degree_sv |
-----------------------------------------------
| 3601464 | 3700 | 1600 | 2200 |
| 1020 | 100 | 0 | 0 |
| 3600520 | 100 | 1300 | 1400 |
| 3600882 | 0 | 100 | 200 |
| 3600520 | 3200 | 800 | 600 |
| 3600520 | 400 | 3000 | 1500 |
-----------------------------------------------
Затем у меня есть еще одна таблица с именами тех степеней.
codes:
------------------------------
| degree_code | degree_text|
------------------------------
| 3700 | Masters |
| 100 | Bachelors |
| 3200 | Doctorate |
| 400 | Diploma A |
| 1600 | High school |
| 1300 | Secondary |
| 800 | Post doc |
| 3000 | Training |
| 2200 | LLB |
| 1400 | M.Sc |
| 200 | B.Sc |
| 600 | Foreign Dip |
| 1500 | Failure |
------------------------------
Я хотел бы иметь что-то вроде этого:
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| user_id | degree_fi | degree_fi_txt | degree_en | degree_en_txt |degree_sv | degree_sv_txt |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 3601464 | 3700 | Masters | 1600 | high school | 2200 | LLB |
| 1020 | 100 | Bachelors | 0 | | 0 | |
| 3600520 | 100,3200,400 | Bachelors, Doctorate, Diploma A | 1300, 800, 3000 | secondary, post doc, Training | 1400, 600, 1500 | M.Sc, Foreign Dip, Failure |
| 3600882 | 0 | | 100 | Bachelors | 200 | B.Sc |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Я пытался использовать функцию listagg следующим образом:
SELECT user_id, listagg(degree_fi, ',') within GROUP (ORDER BY degree_fi) degree_fi,
listagg(SELECT degree_text from codes WHERE degree_code IN (SELECT degree_fi feom degree), ',') within GROUP (ORDER BY degree_text) degree_fi_txt,
listagg(degree_en, ',') within GROUP (ORDER BY degree_en) degree_en,
listagg(SELECT degree_text from codes WHERE degree_code IN (SELECT degree_en feom degree), ',') within GROUP (ORDER BY degree_text) degree_en_txt,
listagg(degree_sv, ',') within GROUP (ORDER BY degree_en) degree_sv
listagg(SELECT degree_text from codes WHERE degree_code IN (SELECT degree_sv feom degree), ',') within GROUP (ORDER BY degree_text) degree_sv_txt,
FROM degree GROUP BY user_id
Но я бьюсь об стену с этим. Любая рекомендация?
Заранее спасибо.