VSQL: Concatenate two values in same column from same table

Question

I have a table that looks like the following:

      email           | first_name 
----------------------+------------
 ------@diffem.com    | Matthew
 ------@email.net     | Susan      
 ------@email.net     | Thomas     
 ------@email.com     | Donald     
 ------@email.com     | 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  
----------------------+-----------------
------@diffem.com    | Matthew
------@email.net     | Susan and Thomas     
------@email.com     | 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.net     | Susan  | null   
 ------@email.net     | null   | Donald

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

score 0 · Answer 1 · answered Jan 25 '21 at 23:39

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/

VSQL: Concatenate two values in same column from same table

1 Answers1