postgresql - Remove two rows into single row in union query -

i have query -

select id,fieldname value1,'' value2 tablename union  select id,'' value1,fieldname value2 tablename 

it give output like--

id  value1  value2 1           name 1   name   2   abc       2           abcx 

but trying display like-

id  value1    value2  1   name      name  2   abc       abcx 

in postgresql.

can 1 suggest me how can it. unpivot work situation.

use string_agg():

select string_agg(value1, '') value1, string_agg(value2, '') value2 (     select 'name' value1, '' value2     union      select '' value1, 'name' value2     ) s;   value1 | value2  --------+--------  name   | name (1 row)      

---

aggregate functions string_agg() executed groups of rows. use group id:

with a_table(id, col1, col2) (     values         (1, 'name', 'name'),         (2, 'abc', 'abcx')     ) select id, string_agg(value1, '') value1, string_agg(value2, '') value2 (     select id, col1 value1,'' value2 a_table     union      select id, '' value1, col2 value2 a_table     ) s group id order id;   id | value1 | value2  ----+--------+--------   1 | name   | name   2 | abc    | abcx (2 rows)