By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE INCIDENTCHAPTER (INCIDENTCHAPTER_ID PRIMARY KEY) AS
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 3
3 rows affected
CREATE TABLE label (INCIDENTCHAPTER_ID, TEXTTYPE, TEXT) AS
SELECT 1, 'Name', 'Alert' FROM DUAL UNION ALL
SELECT 1, 'ShortName', 'A' FROM DUAL UNION ALL
SELECT 1, 'Help', 'Some Helptext' FROM DUAL UNION ALL
SELECT 2, 'Name', 'Notification' FROM DUAL UNION ALL
SELECT 2, 'ShortName', 'N' FROM DUAL UNION ALL
SELECT 2, 'Help', 'Another Helptext' FROM DUAL UNION ALL
SELECT 3, 'Name', 'Chapter One' FROM DUAL UNION ALL
SELECT 3, 'ShortName', '1' FROM DUAL;
8 rows affected
SELECT ic.INCIDENTCHAPTER_ID,
N.TEXT AS NAME,
SN.TEXT AS SHORTNAME,
H.TEXT AS HELP
FROM /*REM_DBA.*/INCIDENTCHAPTER ic
LEFT JOIN LABEL N
ON ( ic.INCIDENTCHAPTER_ID = N.INCIDENTCHAPTER_ID
AND N.TEXTTYPE = 'Name')
LEFT JOIN LABEL SN
ON ( ic.INCIDENTCHAPTER_ID = SN.INCIDENTCHAPTER_ID
AND SN.TEXTTYPE = 'ShortName')
LEFT JOIN LABEL H
ON ( ic.INCIDENTCHAPTER_ID = H.INCIDENTCHAPTER_ID
AND H.TEXTTYPE = 'Help')
| INCIDENTCHAPTER_ID | NAME | SHORTNAME | HELP |
|---|---|---|---|
| 1 | Alert | A | Some Helptext |
| 2 | Notification | N | Another Helptext |
| 3 | Chapter One | 1 | null |
SELECT *
FROM (
SELECT ic.incidentchapter_id,
l.texttype,
l.text
FROM /*REM_DBA.*/INCIDENTCHAPTER ic
LEFT OUTER JOIN LABEL l
ON ( ic.INCIDENTCHAPTER_ID = l.INCIDENTCHAPTER_ID)
)
PIVOT (
MAX(text) FOR texttype IN (
'Name' AS name,
'ShortName' AS shortname,
'Help' AS help
)
)
| INCIDENTCHAPTER_ID | NAME | SHORTNAME | HELP |
|---|---|---|---|
| 1 | Alert | A | Some Helptext |
| 2 | Notification | N | Another Helptext |
| 3 | Chapter One | 1 | null |