CODEWITHSUNDEEP
postgresql
Basavaraj
Basavaraj

Reputation: 1111

Remove two rows into single row in union query

I have query like -

select id,fieldName as value1,'' as value2 from tableName
union 
select id,'' as value1,fieldName as value2 from tableName

it give output like--

id  value1  value2
1           name
1   name  
2   abc      
2           abcx

but trying to display it like-

id  value1    value2
 1   name      name
 2   abc       abcx

in PostgreSQL.

can any one suggest me how can do with it. is unpivot work this situation.

Upvotes: 0

Views: 198

Answers (1)

klin
klin

Reputation: 121824

Use string_agg():

select string_agg(value1, '') value1, string_agg(value2, '') value2
from (
    select 'name' as value1, '' as value2
    union 
    select '' as value1, 'name' as value2
    ) s;

 value1 | value2 
--------+--------
 name   | name
(1 row)

Aggregate functions like string_agg() usually are executed for groups of rows. Use group by id:

with a_table(id, col1, col2) as (
    values
        (1, 'name', 'name'),
        (2, 'abc', 'abcx')
    )
select id, string_agg(value1, '') value1, string_agg(value2, '') value2
from (
    select id, col1 as value1,'' as value2 from a_table
    union 
    select id, '' as value1, col2 as value2 from a_table
    ) s
group by id
order by id;

 id | value1 | value2 
----+--------+--------
  1 | name   | name
  2 | abc    | abcx
(2 rows)

Upvotes: 2

Related Questions