Sometimes there’s a many to one relationship between tables and we really need a one to one in our results. The example below is how you’d accomplish that – it turns results from many rows into one row. The example below uses languages where there is one “name_id” and multiple “languages” in the table. Furthermore a case statement is used to squish multiple columns in the one row into one column.
The row number is used in the result and “partition by” is used to restart counting the row numbers (from 1) based on the specified column (name_id in this case).
-- select all languages in one column
WHEN FIRST IS NOT NULL AND SECOND IS NOT NULL AND third IS NOT NULL THEN
FIRST || ';' || SECOND || ';' || third
WHEN FIRST IS NOT NULL AND SECOND IS NOT NULL THEN
FIRST || ';' || SECOND
END AS all_langs
-- select the name_id and the all of the rows which may have a result
MAX(decode (seq, 1, LANGUAGE, NULL)) FIRST,
MAX(decode (seq, 2, LANGUAGE, NULL)) SECOND,
MAX(decode (seq, 3, LANGUAGE, NULL)) third
FROM (SELECT al.name_id,
ROW_NUMBER () -- use the row number in the result
OVER (PARTITION BY nal.name_id
ORDER BY nal.name_id DESC
NULLS LAST) -- put null values at the end of the list
AS seq -- seq (aka row number) is used in the outer query
FROM JOIN schema_name.languages al
AND nal.name_id = al.name_id
AND al.AFFILIATION_PRIMARY = 'Language'
GROUP BY name_id