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.
CodePudding user response：
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 = ...;
CodePudding user response：
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