sql pivot row values to a single column

Question

I have the following output from a sql query in a row. I'd like to pivot the row values to a single column labeled id:

| id | lag_1 | lag_2 | lag_3 |

| -- | ----- | ----- | ----- |

| 8  | 7     | 6     | 5     | 

expected output:

id 

8
7
6
5

Answer 1

This is known as "unpivot". Different database platforms have different methods available to accomplish. MySQL uses UNION construct to rearrange fields to rows.

SELECT id, 0 AS Src FROM table
UNION ALL
SELECT lag_1, 1 FROM table
UNION ALL
SELECT lag_2, 2 FROM table
UNION ALL
SELECT lag_3, 3 FROM table

Remove ALL if you don't want duplicates.