Postgres中的Concat行 [英] Concat rows in Postgres

问题描述

Given是这样的Postgres表

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

要查询此表，我的sql看起来像这样吧：

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; 

是否可以在 num 相同（在本例中为 2 ）。
意味着我的输出应如下所示（如果数字相等，现金也将具有相同的值）：

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

推荐答案

使用 GROUP BY 和汇总函数 string_agg（） ：

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;

我在<$ c中添加了现金 $ c> GROUP BY 达到原始值，并防止相同的 nummer 有所不同的情况。

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.

关于您的评论：

是否也可以按名称唯一地查询。表示如果2 | andy
london | 700被列出两次，则应将其中一个删除。

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;

这篇关于Postgres中的Concat行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！