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.

"I tried to use the case statement but did not give me desired results." Show us your code and the current results and we can show you how to fix it. — Matt S, Jun 06 '16 at 21:11
Hi @MattS . I apologize, updated my question hope that helps ! — Vinay, Jun 06 '16 at 22:52
please fix your question. u are asking for converting 2 rows to 2 columns. also called transpose rows to columns — junnyea, Sep 27 '20 at 15:24

pgyogesh · Answer 1 · 2016-06-09T15:33:41.980

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

edited Jun 09 '16 at 15:33

answered Jun 09 '16 at 15:20

pgyogesh

342
2
13

Thanks for your answer. But the values in the table here are dynamic. – Vinay Jun 09 '16 at 15:34

score 1 · Answer 2 · edited Jul 09 '19 at 06:43

1

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;

edited Jul 09 '19 at 06:43

MBer

2,218
20
34

answered Jun 09 '16 at 19:13

Syamala

23
4

score 0 · Answer 3 · answered Jun 06 '16 at 21:36

0

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);

answered Jun 06 '16 at 21:36

Jon Roberts

2,068
1
9
11

Sorry. I updated my question now. How about if we have the dynamic values in the column_1. – Vinay Jun 06 '16 at 22:56

score 0 · Answer 4 · answered Jun 09 '16 at 15:44

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

Convert one column to multiple columns in postgres

4 Answers4