How to turn multiple comma separated values in a row into a unique value sql 2017

Question

Data looks like this:

declare @table1 table (ReportName varchar(200), email varchar(100))
insert into @table1 values ('Finance report', 'tony@mail.com, Nico@email.com, greg@email.com')
                          ,('Producer report', 'laura@mail.com, josh@email.com')
select * from @table1

I need it like this:

  **ReportName       email**
Finance report  tony@mail.com
Finance report  Nico@email.com
Finance report  greg@email.com
Producer report laura@mail.com
Producer report josh@email.com

`STRING_SPLIT`, and stop storing your data in a delimited format and fix your design. — Thom A, Dec 31 '19 at 16:50
Does the order of the items in each list have any significance, e.g. "To:" followed by "Cc:"? [`String_Split`](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15) proudly proclaims: "The output rows might be in any order. The order is _not_ guaranteed to match the order of the substrings in the input string." — HABO, Dec 31 '19 at 17:02

score 1 · Accepted Answer · answered Dec 31 '19 at 16:51

1

You can use apply and split the string:

select t1.reportname, s.value as email
from table1 t1 cross apply
     string_split(replace(t1.email, ', ', ','), ',') s;

Here is a db<>fiddle.

answered Dec 31 '19 at 16:51

Gordon Linoff

1,242,037
58
646
786

How to turn multiple comma separated values in a row into a unique value sql 2017

1 Answers1