How can I combine multiple rows into one column using PostgreSQL?

Question

How can I combine multiple results into one column?

For example, my query displays this result:

internaldocid | document id | versionid | title | tagname
1146 | DOC-1146 | 2 | Press Release | tag1
1146 | DOC-1146 | 2 | Press Release | tag2
1146 | DOC-1146 | 2 | Press Release | tag3

I would like it to display this result:

internaldocid | document id | versionid | title | tagname
1146 | DOC-1146 | 2 | Press Release | tag1, tag2, tag3

Here is my SQL:

SELECT
  cmsdocument.internaldocid,
  cmsdocument.documentid,
  cmsdocument.versionid,
  cmsdocversion.title,
  cmstag.tagname
FROM 
  public.cmsobjecttag,
  public.cmstag,
  public.cmsdocument,
  public.cmsdocversion,
  public.cmsuser
WHERE 
  cmsobjecttag.tagid = cmstag.tagid AND
  cmsobjecttag.objectid = cmsdocument.internaldocid AND
  cmsdocument.internaldocid = cmsdocversion.internaldocid AND
  cmsdocument.userid = cmsuser.userid AND
  cmsdocversion.state = 'published' AND
  cmsobjecttag.objecttype = '102';

Any help would be greatly appreciated.

take a look pivot table... craigkerstiens.com/2013/06/27/Pivoting-in-Postgres — HaveNoDisplayName
– HaveNoDisplayName, Commented Apr 23, 2015 at 19:20

Adriano Carneiro · Accepted Answer · 2015-04-23 19:23:57Z

2

Since PostgreSQL version 9, this is done simply by using string_agg:

SELECT
  cmsdocument.internaldocid,
  cmsdocument.documentid,
  cmsdocument.versionid,
  cmsdocversion.title,
  string_agg(cmstag.tagname, ',')
FROM 
  public.cmsobjecttag,
  public.cmstag,
  public.cmsdocument,
  public.cmsdocversion,
  public.cmsuser
WHERE 
  cmsobjecttag.tagid = cmstag.tagid AND
  cmsobjecttag.objectid = cmsdocument.internaldocid AND
  cmsdocument.internaldocid = cmsdocversion.internaldocid AND
  cmsdocument.userid = cmsuser.userid AND
  cmsdocversion.state = 'published' AND
  cmsobjecttag.objecttype = '102'
GROUP BY 
  cmsdocument.internaldocid,
  cmsdocument.documentid,
  cmsdocument.versionid,
  cmsdocversion.title

answered Apr 23, 2015 at 19:23

Adriano Carneiro

58.8k12 gold badges94 silver badges123 bronze badges

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

1 Comment

Adriano Carneiro Over a year ago

@Curtis don't forget to upvote the helping answers and to accept the answer that best suits your needs.

Collectives™ on Stack Overflow

How can I combine multiple rows into one column using PostgreSQL?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related