Merge rows in postgresql

Question

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?

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
– Raymond Nijland, Commented Oct 11, 2018 at 10:36
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
– Gordon Linoff, Commented Oct 11, 2018 at 10:51
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
– Raymond Nijland, Commented Oct 11, 2018 at 11:06

Zaynul Abadin Tuhin · Accepted Answer · 2018-10-11 10:11:45Z

2

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

edited Oct 11, 2018 at 10:11

answered Oct 11, 2018 at 10:04

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

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

Comments

Yogesh Sharma · Accepted Answer · 2018-10-11 10:09:15Z

1

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;

answered Oct 11, 2018 at 10:09

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Collectives™ on Stack Overflow

Merge rows in postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related