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.
Advertisement
Answer
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;