need some help to construct a PostgreSQL query. I am trying to combine various rows in a Postgres table based on a certain condition.

Here's what the table looks like

I want to concatenate all address values of a specific role (eg. home, school, office) into one column. Address type can contain values like Address Line 1 to 8. Here, Home has Address Line 1 to 4 whereas office has Address Line 1 to 3.

CodePudding user response：

Use array_agg() function for combining column value with comma. Here ORDER BY clause isn't used because address line 4 wouldn't come before address line 1. Extra ORDER BY clause can degrade query performance for a large data sets

-- PostgreSQL
SELECT roll_no, role
     , role || ' Address' address_type
     , array_to_string(array_agg(address_value), ', ') address_value  
FROM test
GROUP BY roll_no, role
ORDER BY roll_no, role;

Please check this url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=e46cf351452a2715258b69afeea5c742

If ORDER BY must needed inside array_agg() function then use the below query

-- after applying order by inside array_agg()
SELECT roll_no, role
     , role || ' Address' address_type
     , array_to_string(array_agg(address_value order by address_type), ', ') address_value  
FROM test
GROUP BY roll_no, role
ORDER BY roll_no, role;

Please check the url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=c196a9a2ca71bd886e4750b935d23040

If same address stored for multiple address line of a particular role for specific roll_no then DISTINCT keyword will use inside array_agg().

-- after applying distinct inside array_agg()
SELECT roll_no, role
     , role || ' Address' address_type
     , array_to_string(array_agg(DISTINCT address_value), ', ') address_value  
FROM test
GROUP BY roll_no, role
ORDER BY roll_no, role;

Please check this url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=e4f0de7c37e003133e2539149db245f8

CodePudding user response：

You may use ARRAY_AGG() inside ARRAY_TO_STRING() but with caution. Because generally, SQL tables are unordered sets. Hence explicitly mentioning ORDER BY inside array_agg() is very very important.

Code:

SELECT 
    Roll_No, 
    role,
    role || ' address' AS address_type, 
    array_to_string(array_agg(addressvalue ORDER BY roll_no, role, addresstype), ', ') as address_value
FROM t
GROUP BY Roll_No, role
ORDER BY roll_no, role, address_value

Look at the db<>fiddle. Take a look how results vary with and without ORDER BY inside array_agg().