SELECT ID,NAME,VALUE1,VALUE2 FROM my_table where ID=1 ;
该查询将为我提供如下输出:
ID|NAME|VALUE1|VALUE2
1|XYZ|123|325
但是我希望输出的格式如下所示:
ID |1
NAME |XYZ
VALUE1|123
VALUE2|325
我的列将是固定的,每次结果都将是单行,我该如何修改查询以获得下面的结果?
SELECT ID,NAME,VALUE1,VALUE2 FROM my_table where ID=1 ;
ID|NAME|VALUE1|VALUE2
1|XYZ|123|325
ID |1
NAME |XYZ
VALUE1|123
VALUE2|325
请查看此示例,它恰好可以满足您的需求。
评论更新:
但如果我有多列和多行,怎么办?如何进行转置?
我的UDF将为每一行执行转置/展开操作,例如:
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)
一种符合ANSI标准的方法是只使用一系列联合体:
SELECT 'ID' AS key, ID AS value FROM my_table WHERE ID=1
UNION ALL
SELECT 'NAME', NAME FROM my_table WHERE ID=1
UNION ALL
SELECT 'VALUE1', VALUE1 FROM my_table WHERE ID=1
UNION ALL
SELECT 'VALUE2', VALUE2 FROM my_table WHERE ID=1;
这个也应该可以工作
SELECT StringTokenizerDelim((ID || ',' || NAME || ',' || VALUE1 || ',' || VALUE2), ',') over (partition by null) FROM my_table where ID = 1