Concat rows in Postgres

Question

Given is a Postgres table like this

nummer  vorname    name        cash
------|-----------|-----------|-----
1       paul       smith       500
2       andy       london      700
2       mike       dover       700
3       clara      winchester  200

To query this table my sql looks like this right know:

SELECT 
  nummer,
  vorname,
  name,  
  cash as total
FROM 
  data.myTable
GROUP BY
  nummer, 
  name,
  vorname,
  cash
ORDER BY
  nummer;

Is it possible to concat the two rows where nummer is the same (in this case 2). Means my output should look like this (cash will also have the same values if the numbers are equal):

nummer  vorname    name        cash
------|-----------|-----------|-----
1       paul       smith       500
2       andy       london      700
        mike       dover       
3       clara      winchester  200

Erwin Brandstetter · Accepted Answer · 2012-09-11 13:47:51Z

10

Use GROUP BY and the aggregate functioin string_agg():

SELECT nummer
      ,string_agg(vorname, E'\n') AS vorname
      ,string_agg(name, E'\n') AS name
      ,cash
FROM   mytable
GROUP  BY nummer, cash
ORDER  BY nummer, cash;

I added cash to the GROUP BY to get to the original value and safeguard against the case where it would be different for the same nummer.

As to your comment:

is it possible to unique the query also by names. means if 2|andy london|700 is listed twice one should be removed.

SELECT nummer
      ,string_agg(vorname, E'\n') AS vorname
      ,string_agg(name, E'\n') AS name
      ,cash
FROM  (
   SELECT DISTINCT
          nummer, vorname, name, cash
   FROM   mytable
   ) AS m
GROUP  BY nummer, cash
ORDER  BY nummer, cash;

edited Sep 11, 2012 at 13:47

answered Sep 11, 2012 at 13:03

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

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

1 Comment

Erwin Brandstetter Over a year ago

@tBook: Note that I removed DISTINCT from the aggregate function. Was an oversight. You could use that to remove dupes per column (which you did not specify).

Chris Travers · Accepted Answer · 2012-09-11 13:26:53Z

1

SELECT nummer, 
       array_to_string(array_agg(vorname), E'\n'), 
       array_to_string(array_agg(name), E'\n'),
       cash
  FROM mytable
  group by nummer, cash;

That should do it.

edited Sep 11, 2012 at 13:26

answered Sep 11, 2012 at 12:58

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

4 Comments

Anatol Over a year ago

thanks a million this works great, i recongnized one more issue. is it possible to unique the query also by names. means if 2|andy london|700 is listed twice one should be removed. -> in other word how can i add an distinct on name, vorname, cash ? thanks tony

Chris Travers Over a year ago

fixed it. As to get rid of the duplicate names, that's a tough one. you have some issues here regarding data dependencies. Working on this side. one sec.

Anatol Over a year ago

@Chris, thanks for your input. The cash in the "group by" is a good step, this works. regarding the names. in this case it should look like the second code example in my first post. This works so far execpt the duplicate names. in other words in those cases nummer and cash are always the same. nummer and cash should be merged (works!), names should be merged and duplicated should be kicked out. possible? my other idea word be to save all data in a php array and do something like array unique … kind regards, tony

Anatol Over a year ago

@Chris: got it with a small adaption from erwins snippet, I would post it but not sure how to do it correctly on stackoverflow. thanks both of YOU a lot!

Collectives™ on Stack Overflow

Concat rows in Postgres

2 Answers 2

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related