如何使用PostgreSQL将多行合并为一列？ [英] How can I combine multiple rows into one column using PostgreSQL?

问题描述

如何将多个结果合并到一列中？

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

这是我的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';

任何帮助将不胜感激。

推荐答案

从PostgreSQL版本9开始，只需使用 string_agg ：

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

这篇关于如何使用PostgreSQL将多行合并为一列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！