0

I have a table like this:

+----+-------+-----------------+
| ID | Name  |      Email      |
+----+-------+-----------------+
|  1 | Jane  | Jane@doe.com    |
|  2 | Will  | Will@gmail.com  |
|  3 | Will  | wsj@example.com |
|  4 | Jerry | jj2@test.com    |
+----+-------+-----------------+

Unfortunately I have records that are duplicates due to multiple emails. I would like to run a sql query to generate this:

+----+-------+---------------------------------+
| ID | Name  |              Email              |
+----+-------+---------------------------------+
|  1 | Jane  | Jane@doe.com                    |
|  2 | Will  | Will@gmail.com, wsj@example.com |
|  4 | Jerry | jj2@test.com                    |
+----+-------+---------------------------------+

I know with numbers you'd do something like this, but I don't know how to 'sum' text fields:

SELECT *,
   SUM(Number_Field) AS Number_Field,
FROM table

Thanks!

Edit: I am using MS Access

user3606616
  • 33
  • 4
  • which dbms are you using? – Barbaros Özhan Jul 06 '18 at 16:24
  • @BarbarosÖzhan Oh, sorry, I am using MS Access – user3606616 Jul 06 '18 at 16:26
  • Why would you want to fold the email fields in the db ? Its probably a better design to keep the db as is and aggregate the email addresses in the client application. Also note that depending on text lengths and number of different email addresses the concatenated string may break the dimension of the target column. Anyway there is no standard sql aggregation function acting as a list aggregator. [This SO post](https://stackoverflow.com/questions/9358691/sql-aggregate-function-to-obtain-a-list) contains some rdbms-specific solutions (unfortunately not for ms access). – collapsar Jul 06 '18 at 16:27
  • @collapsar The software we are importing it into isn't capable of handling that. I would rather run a quick process like this right before exporting it to csv. – user3606616 Jul 06 '18 at 16:31
  • For the record: `LISTAGG` seems to have been adopted in a recent version of the SQL standard according to [this source](https://modern-sql.com/feature/listagg). – collapsar Jul 06 '18 at 16:38
  • 1
    Possible duplicate of [ConcatRelated function in a query](https://stackoverflow.com/questions/18940038/concatrelated-function-in-a-query) – June7 Jul 07 '18 at 01:37

0 Answers0