CODEWITHSUNDEEP
postgresqlpivotpostgresql-9.2greenplum
Vinay
Vinay

Reputation: 237

Convert one column to multiple columns in postgres

I have a table like this below :

enter image description here

Would like to change the format as below on postgres :

enter image description here

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.

Upvotes: 3

Views: 4655

Answers (4)

Syamala
Syamala

Reputation: 23

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;

Upvotes: 1

Kyle Dunn
Kyle Dunn

Reputation: 370

If the result you want to transpose always has only 2 rows, this will work regardless of the contents of those columns, as you asked:

SELECT
MAX(CASE WHEN row_number=1 THEN column_1 END) column_1,
MAX(CASE WHEN row_number=2 THEN column_1 END) column_2
FROM (SELECT column_1, 
             ROW_NUMBER() OVER (ORDER BY test_table.column_1) 
      FROM test_table) t;

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

Upvotes: 0

pgyogesh
pgyogesh

Reputation: 352

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

Upvotes: 1

Jon Roberts
Jon Roberts

Reputation: 2106

You didn't really provide enough information to really answer the question but this is how you convert those two rows from one column into two columns and forced into a single row.

select max(column_1) as column_1, max(column_2) as column_2
from (select case when column_1 = 'A' then column_1 else '' end as column_1,
             case when column_1 = 'B' then column_1 else '' end as column_2
      from table_name);

Upvotes: 0

Related Questions