sqlpostgresqlsortingpivotfull-outer-join

Sort columns independently, such that all nulls are last per column


Here is an example table called animal:

name | color
------------
fox  | brown
fox  | red
dog  | gold

Now, what I want is this result:

fox   | dog
-------------
brown | gold
red   | 

The names should be columns of the result with the different color values as rows.

My first thought was like:

SELECT color
FROM animal
WHERE name='fox'

[some sort of join?]

SELECT color 
FROM animal
WHERE name='dog'

But I don't know what kind of join would do the trick.

Second thought:

SELECT CASE WHEN name = 'fox' THEN color ELSE NULL END AS fox,
CASE WHEN name = 'dog' THEN color ELSE NULL END AS dog
FROM animal

This returns:

fox   | dog
-----------
red   | 
brown |
      | gold

I would like to move the null values in this table to the end. I tried to:

ORDER BY CASE name
        WHEN 'fox' THEN fox
        WHEN 'dog' THEN dog
    END

But I'm not sure if this is really what I want and Postgres is nagging that fox is not a column although I can do ORDER BY fox.

Maybe my approach is total nonsense or there is some kind of coalesce magic that can do the trick?


Solution

  • You seem to be under the impression that there would be a "natural" order in a table (like in a spreadsheet), but there is not. Without ORDER BY, rows are returned in arbitrary order - which often happens to be identical to input order for small tables that have not been updated, yet.

    WITH cte AS (
       SELECT row_number() OVER (PARTITION BY name ORDER BY color) AS rn, * 
       FROM   animal
       )
    SELECT f.color AS fox, d.color AS dog
    FROM        (SELECT rn, color FROM cte WHERE name = 'fox') f
    FULL   JOIN (SELECT rn, color FROM cte WHERE name = 'dog') d USING (rn)
    ORDER  BY rn;
    

    Major points

    Attach sequential numbers per color for each type of animal separately in a CTE.

    The FULL [OUTER] JOIN is crucial, since the number of rows for 'fox' and 'dog' differ.

    Colors are sorted alphabetically, NULL values are last automatically in default ASCENDING sort order. See:

    This only scans the table once.

    Explanation for the error message you got ("fox" is not a column):