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;
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, since they are filled in for missing rows at the end of the table by the
FULL [OUTER] JOIN.
- This only scans the table once.
Explanation for the error message you got ("fox" is not a column):
- PostgreSQL Where count condition
- GROUP BY + CASE statement
Try something like:
WITH fox_color as
(SELECT color as fox, row_number() rn
FROM animal
WHERE name='fox'),
dog_color as
(SELECT color as dog, row_number() rn
FROM animal
WHERE name='dog')
SELECT fox, dog
FROM fox_color
JOIN dog_color ON fox_color.rn = dog_color.rn
