How to convert Rows to Columns in postgresql query

Question

I have a simple query output of two rows and single column

Virginia
Texas

I want the output as

Virginia   |  Texas

I just tried it as two subqueries in column list.

select 
(select state from table where code='VA') as state1
(select state from table where code='TX') as state2
from tablename

Is there better way to get the result

Gordon Linoff · Accepted Answer · 2019-07-26 10:35:17Z

1

Are you looking for string aggregation?

select string_agg(state, ' | ' order by code desc)
from table
where code in ('VA', 'TX')

You can use conditional aggregation:

select max(state) filter (where code = 'VA'),
       max(state) filter (where code = 'TX')       
from t
where code in ('VA', 'TX')

answered Jul 26, 2019 at 2:22

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hi, I'm actually looking for two columns as output. column1 should have VA and column2 should have TX