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?
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;
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):