从Postgres中的多行到单列 [英] From multiple rows to single column in Postgres
本文介绍了从Postgres中的多行到单列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在通过具有只读访问权限的pgAdmin 4使用Postgres 9.5,并且我正尝试编写一个select查询,该查询将从这种形式转换数据:
I am using Postgres 9.5 via pgAdmin 4 with read only access and im trying to write a select query that converts data from this form:
+----------+-------------------+--------------------+----------------+
| 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 |
+----------+-----------------------------------------------+-----------------------------------------------------+---------------------+
请注意,列过滤器,组和角色中的值可能会更改.
Noting that values in columns filters, groups and roles might change.
是可以执行该操作的postgres选择脚本吗?
Is the a postgres select script that can perform that?
非常感谢!
推荐答案
尝试一下
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;
这篇关于从Postgres中的多行到单列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文