VSQL: Concatenate two values in same column from same table

Question

I have a table that looks like the following:

      email           | first_name 
----------------------+------------
 [email protected]    | Matthew
 [email protected]     | Susan      
 [email protected]     | Thomas     
 [email protected]     | Donald     
 [email protected]     | Paula      

I.e. I have records where there is only one value (name) per key (email), but in other instance I have two values per key.

I want the output to look like this:

     email           | first_name  
----------------------+-----------------
[email protected]    | Matthew
[email protected]     | Susan and Thomas     
[email protected]     | Donald and Paula  

I have tried the following, but it is not working due to grouping by an aggregate function:

CREATE TABLE user.table1 AS 
(
select distinct email
        , case when email_count = 1 then first_name
               when email_count = 2 then (MIN(first_name))||' and '||MAX(first_name))
          else null end as first_name_grouped
FROM (
     SELECT email
          , first_name
          , count(email) over (partition by email) as email_count 
     FROM table
     )
x
)
;

I've also tried partitioning by email, putting the two names into different columns and then concatenating that, but am ending up with blanks in my output table (see below)

      email           | name1  | name 2
----------------------+--------+-------
 [email protected]     | Susan  | null   
 [email protected]     | null   | Donald

Is there a way to do this in SQL, without creating two separate name columns? Thanks in advance.

Answer 1

What you are trying to accomplish could be done in MYSQL like

SELECT email, GROUP_CONCAT(first_name)
FROM table
GROUP BY email

There is similar function in MS SQL server called STRING_AGG() , you can see more here https://database.guide/mysql-group_concat-vs-t-sql-string_agg/