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
)
 Posted by at 9:22 pm

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)