How to convert column values into rows PostgreSQL

Question

This is my query:

SELECT * FROM profile_change_set where id = '1558079b-d954-4a0d-b241-b39fe8f3498c';

id	table_name	column_key	column_value	operation_type
1558079b-d954-4a0d-b241-b39fe8f3498c	farmers	smsEnabled	somvalue	update

What is want is to convert all values into rows.

column_key	column_value
id	1558079b-d954-4a0d-b241-b39fe8f3498c
table_name	farmers
column_key	smsEnabled
column_value	somvalue
operation_type	update

Any help, thank you in advance.

If you are using `psql`, you can turn that output on using the `\x` [meta command](https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMANDS) — , Sep 15 '22 at 07:57

score 1 · Accepted Answer · answered Sep 15 '22 at 08:00

1

You can use JSON features to turn columns into rows:

select cols.*
from profile_change_set pcs
  cross join jsonb_each_text(to_jsonb(pcs)) as cols(column_key, column_value)
where pcs.id = ...;

answered Sep 15 '22 at 08:00

score -1 · Answer 2 · answered Sep 15 '22 at 08:05

-1

Try use table function CROSSTAB (analogue PIVOT function in SQL Server)

And take a look at this answer, in that answer, an intermediate table is used as a variable, and after that on this variable use CROSSTAB. Take a look at this way

answered Sep 15 '22 at 08:05

Johann Schatten

19
4

How to convert column values into rows PostgreSQL

2 Answers2