Merge rows in postgresql

1
votes

I have a table with the following values in columns:

Table (col1, col2, col3, col4, col5, col6): 

    a b c d e f
    a b c g h i
    a b c k l m
    a b c n o p

As a result I want to have one row:

a b c d e f g h i k l m n o p

How to do that?

2
sqlpostgresql
Columns names with incremention in one table are suspect and a good candidate for normalization.. Then you should have simply done SELECT DISTINCT col FROM table - Raymond Nijland
Your question is not quite as clear as you think it is. A SQL query has a fixed number of columns. How many columns are in your result set? - Gordon Linoff
@GordonLinoff, 15 - techkuz
Pretty much easier would be to make a pivot query which supports up the 26 columns for a-z range and set the other columns to NULL if there isn't matching data.. Then you won't have to implement PostgreSQL's dynamic SQL feature to generate columns on the fly. - Raymond Nijland

2 Answers

2
votes

use union and string_agg

select string_agg(distinct c ,' ') from
   (
    select col1 as c from t
    union
    select col2 from t
    union
    select col3 from t
    union
    select col4 from t
    union
    select col5 from t
    union
    select col6  from t
  ) as t1
1
votes

I would use string_agg() :

select string_agg(t.col, ' ') 
from (select col1 as col 
      from t
      union
      select col2 
      from t
      union
      . . .
      select col6 
      from t
     ) t;