Convert one column to multiple columns in postgres

Question

I have a table like this below :

Would like to change the format as below on postgres :

I tried to use the case statement but did not give me desired results. Thank you in advance for the help !

EDIT

select (case when column_1='A' then column_1 else 'other' end) column_1,
(case when column_1='B' then Column_1 else 'other' end) column_2 from test_t
 where id= random_value;

Each time the query returns only 2 rows and the row values in the column_1 are dynamic and not fixed.

Answer 1

Here we go...

CREATE TABLE test_table(column_1 text);

INSERT INTO test_table ('A'),('B');

SELECT * FROM test_table ;

column_1
---------
B
A

SELECT
max(case when column_1='A' THEN column_1 END) column_1,
max(case when column_1='B' THEN column_1 END) column_2
from test_table;

column_1 | column_2
----------+----------
 A        | B

In PostgreSQL you can do this easily with crosstab(), but in greenplum still it is not implemented

Answer 2

Please refer to this link. Previously answered.

stackoverflow.com/a/10625294/1870151

SELECT
    unnest(array['col1', 'col2', 'col3']) AS "Columns",
     unnest(array[col1::text, col2::text, col3::text]) AS "Values"
FROM tbl;