postgresql行转列

创建表

CREATE TABLE "public"."tb_user" (
  "id" int4 NOT NULL,
  "name" varchar(255) COLLATE "pg_catalog"."default",
  "age" int4,
  "sex" varchar(255) COLLATE "pg_catalog"."default"
);

INSERT INTO "public"."tb_user" VALUES (1, '张三', 15, '男');
INSERT INTO "public"."tb_user" VALUES (2, '李四', 18, '男');
INSERT INTO "public"."tb_user" VALUES (3, '菲菲', 16, '女');

select * from tb_user;

行转列使用concat_ws()和regexp_split_to_table()函数

select regexp_split_to_table(s.keys,',') as keys,
       regexp_split_to_table(s.values,',') as values
from (
select concat_ws(',','姓名','年龄','性别') as keys,
       concat_ws(',',name,age,sex) as values 
from tb_user
) s;