PostgreSQL simply transpose/convert rows to columns

Question

I read some SO posts like sql server 2008 r2 - Transpose rows to columns - Stack Overflow or sql - Postgres - Transpose Rows to Columns - Stack Overflow, but they are too complicated task to what I want to implement, and what I want to is pretty simple task.

For example, assume I have something like following one:

value
1
2
3

I want to convert the above to something like this:

tmp, tmp02, tmp03
1, 2, 3

The columns, tmp to tmp03, can be manually assigned for me, since the data I'm trying to is 4 or 5 of these, just simpler the sqi is the better.

Answer 1

You could use a pivot query with the help of ROW_NUMBER:

WITH cte AS (
    SELECT value, ROW_NUMBER() OVER (ORDER BY value) rn
    FROM yourTable
)

SELECT
    MAX(value) FILTER (WHERE rn = 1) AS tmp,
    MAX(value) FILTER (WHERE rn = 2) AS tmp02,
    MAX(value) FILTER (WHERE rn = 3) AS tmp03
FROM cte;

Demo