PgSql 行转列的使用 jsonb_each与row_to_json

PgSql 行转列的使用 jsonb_each与row_to_json

1:正常的几行数据

2：转换后

3：code （以commodity来分组）

select "Id" , "JabilPn" ,"Brand" ,"PricelnUsd" ,"Commodity"  from "Eme_Materials" em where "Id" <=3



select "Commodity",(q.qq).key as "key",(q.qq).value as "value"  from (
with a as (
select "Id" , "JabilPn" ,"Brand" ,"PricelnUsd" ,"Commodity"  from "Eme_Materials" em where "Id" <=3
)
select "Commodity" ,jsonb_each(row_to_json(a)::jsonb-'Commodity'::varchar) as qq  from a
)q