1. 首页
  2. 问题
  3. 从Postgres中的多行到单列
小编典典

从Postgres中的多行到单列

sql

我正在通过具有只读访问权限的pgAdmin 4使用Postgres 9.5，我正在尝试编写一个select查询，该查询将从这种形式转换数据：

+----------+-------------------+--------------------+----------------+
| username |      filters      |       groups       |     roles      |
+----------+-------------------+--------------------+----------------+
| kd24     | Khaled  <27607>   | V1                 | NewsStand User |
| kd24     | Khaled  <27607>   | V1                 | User           |
| kd24     | Khaled  <27607>   | Weekly KPIs        | NewsStand User |
| kd24     | Khaled  <27607>   | Detailed Sales     | User           |
| kd24     | Khaled  <27607>   | Qanz Monthly Group | User           |
| kd24     | Khaled  <27607>   | Detailed Sales     | NewsStand User |
| kd24     | Khaled  <27607>   | Qanz Monthly Group | NewsStand User |
| kd24     | Khaled  <27607>   | Weekly KPIs        | User           |
| kd24     | Khaled  <F_27607> | Weekly KPIs        | User           |
| kd24     | Khaled  <F_27607> | Weekly KPIs        | NewsStand User |
| kd24     | Khaled  <F_27607> | Qanz Monthly Group | NewsStand User |
| kd24     | Khaled  <F_27607> | Detailed Sales     | User           |
| kd24     | Khaled  <F_27607> | V1                 | User           |
| kd24     | Khaled  <F_27607> | Detailed Sales     | NewsStand User |
| kd24     | Khaled  <F_27607> | Qanz Monthly Group | User           |
| kd24     | Khaled  <F_27607> | V1                 | NewsStand User |
| kd24     | khaled.d          | Weekly KPIs        | User           |
| kd24     | khaled.d          | V1                 | NewsStand User |
| kd24     | khaled.d          | Detailed Sales     | NewsStand User |
| kd24     | khaled.d          | Qanz Monthly Group | NewsStand User |
| kd24     | khaled.d          | Weekly KPIs        | NewsStand User |
| kd24     | khaled.d          | V1                 | User           |
| kd24     | khaled.d          | Detailed Sales     | User           |
| kd24     | khaled.d          | Qanz Monthly Group | User           |
+----------+-------------------+--------------------+----------------+

填写此表格：

+----------+-----------------------------------------------+-----------------------------------------------------+---------------------+
| username |                    filters                    |                       groups                        |        roles        |
+----------+-----------------------------------------------+-----------------------------------------------------+---------------------+
| kd24     |  Khaled  <27607>, Khaled  <F_27607>,khaled.d  | V1, Weekly KPIs, Detailed Sales, Qanz Monthly Group | NewStand User, User |
+----------+-----------------------------------------------+-----------------------------------------------------+---------------------+

请注意，列过滤器，组和角色中的值可能会更改。

可以执行该操作的postgres选择脚本吗？

非常感激！


阅读 264

收藏
2021-04-28

共1个答案

小编典典

试试这个

SELECT
    username,
    string_agg(DISTINCT filters,',') as filters,
    string_agg(DISTINCT groups,',') as roles,
    string_agg(DISTINCT roles,',') as groups
FROM
    table1
GROUP BY
    username;

演示版

2021-04-28