Convert single row to multiple columns Vertica

Question

SELECT ID,NAME,VALUE1,VALUE2 FROM my_table where ID=1 ;

The query would give me output like

ID|NAME|VALUE1|VALUE2
1|XYZ|123|325

But I want the output in the below format

ID    |1
NAME  |XYZ
VALUE1|123
VALUE2|325

My columns will be fixed and each time the result will be a single row, how can I modify the query to get the below result?

Answer 1

Check this example, it do exactly what you need.

UPDATE on comment:

but what if i have multiple columns and rows how can i transpose

My UDF will do transpose/unpivot for each row, for example:

daniel=> select * from test limit 4;
 user_id | day_of_week | to_sample
---------+-------------+-----------
       1 |           1 |         1
       1 |           2 |         2
       2 |           1 |         1
       2 |           2 |         2
(4 rows)

daniel=> select unpivot(*) over () from test limit 8;
     KEY     | VALUE
-------------+-------
 user_id     | 1
 day_of_week | 1
 to_sample   | 1
 user_id     | 1
 day_of_week | 2
 to_sample   | 2
 user_id     | 2
 day_of_week | 1
(8 rows)