Postgresql implements SQL multi-line statement merges one line

Multi-line statement merges one line

The result sets of three table association queries are:

SELECT ,,, FROM users,users_roles,roles WHERE users_roles.userid =  and users_roles.roleid = 

name age birthday role
Zhang San 23 1993-1-1 role1
Zhang San 23 1993-1-1 role2
Zhang San 23 1993-1-1 role3

After the following statement is executed

SELECT ,,, array_to_string(ARRAY(SELECT unnest(array_agg()) ),',') as roles FROM users,users_roles,roles WHERE users_roles.userid = and users_roles.roleid = group by ,,

The result is:

name age birthday roles

Zhang San 23 1993-1-1 role1, role2, role3

Supplement: PostgreSQL merge query multiple records in the same column

Yesterday I encountered an SQL problem, and I needed to splice multiple rows of data from a column in a table into one row

For example: There are 2 columns of data in the table:

List1     List2

AAA     enterprise1

AAA     enterprise2

AAA     enterprise3

BBB     enterprise4

BBB     enterprise5

I want to turn this table into the following format:

List1       List2

AAA       enterprise1，enterprise2，enterprise3

BBB       enterprise4，enterprise5

How to implement stuff functions similar to sqlServer in PostgreSQL?

I checked some information and found two simple and convenient methods to summarize.

Method 1:

SELECT
 List1, string_agg(List2,',') AS Alias
FROM
 Table name
GROUP BY List1 ;

Method 2 (recommended):

SELECT 
  List1, array_to_string(ARRAY(SELECT unnest(array_agg(List2))),',') AS Alias
FROM 
  Table name
GROUP BY List1;

If you need to sort it and then splice it:

SELECT 
  List1, array_to_string(ARRAY(SELECT unnest(array_agg(List2 order by List2 desc))),',') AS Alias
FROM 
  Table name
GROUP BY List1;

The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.