我通过 pgAdmin 4 使用具有只读访问权限的 Postgres 9.5,并且尝试编写一个选择查询来转换来自此表单的数据:
+----------+-------------------+--------------------+----------------+
| 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 select 脚本可以执行此操作吗?
非常感激!
Try this
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;
Demo http://sqlfiddle.com/#!17/61da1/4
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)