pgsql 列转行

1单表查询结果出来只有一行结果

select * from table;

2现在将查询结果由列转为行展示

select unnest(string_to_array(concat_ws(',',a.t_dev_names,a.t_dev_ids,a.T_PORT_NAMES,a.T_PORT_IDS),',')) as result from table as a

步骤分析:

concat_ws:将几个字段合并为一个字段并用逗号拼接成字符串

select concat_ws(',',a.t_dev_names,a.t_dev_ids,a.T_PORT_NAMES,a.T_PORT_IDS)as result from table as a

string_to_array:将上面拼接的字符串转成数组

select string_to_array(concat_ws(',',a.t_dev_names,a.t_dev_ids,a.T_PORT_NAMES,a.T_PORT_IDS),',')as result table as a

unnest:将上面的数组转行