Many Rows to One Row

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 DISTINCT

       name_id,

       

       -- select all languages in one column

       CASE

           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



           ELSE FIRST



       END AS all_langs



FROM (

        -- select the name_id and the all of the rows which may have a result

    SELECT DISTINCT

           name_id,

           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,

                 al.LANGUAGE,

                 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

)