Save a comma separated array in a string in a table [duplicate]

Question

I inherited a table that has these columns

ID,  Name,  Subjects
--   ----   --------
33   Mike   Math,English,Physics
24   Paul   Art,French,Med,English,Math
58   Sami   Physics,Biology
22   Nora   Math,English,Art
76   Mona   Math,English,French,Med,Physics
39   Lila   Physics
19   Dave   Math,Biology,Physics
48   Jade   English,French,Physics
82   Mark   Med,Biology,Physics
23   Nina   Biology,English,Physics

I am trying to break this into my structured table.

ID,  Name,  Subject
--   ----   --------
33   Mike   Math
33   Mike   English
33   Mike   Physics
24   Paul   Art
24   Paul   French
24   Paul   Med
24   Paul   English

I tried with using STRING_SPLIT in the select statement

SELECT ID, Name, STRING_SPLIT(Subjects, ',') AS SUbject
FROM Students

but that did not work

'STRING_SPLIT' is not a recognized built-in function name.

How can I split these subjects into rows?

This script should generate the table and data

declare @Students as table (ID int, Name varchar(4), Subjects varchar(100))

INSERT INTO @Students (ID, Name, Subjects)
VALUES 
(33,'Mike','Math,English,Physics'),
(24,'Paul','Art,French,Med,English,Math'),
(58,'Sami','Physics,Biology'),
(22,'Nora','Math,English,Art'),
(76,'Mona','Math,English,French,Med,Physics'),
(39,'Lila','Physics'),
(19,'Dave','Math,Biology,Physics'),
(48,'Jade','English,French,Physics'),
(82,'Mark','Med,Biology,Physics'),
(23,'Nina','Biology,English,Physics')

SELECT * FROM @Students

Response to suggested duplicate

Although this question was closed suggesting it similar to Split function equivalent in T-SQL? it is actually not.

That question is a simple like which a simple FROM string_split() can work for. I got the answer thanks to Gordon. I thought it might help other with the same issue. If you have a similar issue, you may find the answer down.

@JonathanWillcock no, my issue is not one string, it is a string in each row in the table. this is a different issue — asmgx
– asmgx, Commented Jan 2, 2021 at 1:51
No it's not. For earlier versions of SQL Server, you solve it with a table valued function. — Jonathan Willcock
– Jonathan Willcock, Commented Jan 2, 2021 at 1:53
@JonathanWillcock I have a CSV in each row in the table.. the question you proposed is for a single CSV.. SQL version is not the issue — asmgx
– asmgx, Commented Jan 2, 2021 at 1:55
The proposed structure is a definite improvement over delimited data, but you can do even better. A further improvement would use two tables: one with (ID, Name), where each ID value only appears once, and the other with (ID, Subject), where each ID value can appear in several rows. This protects you from certain classes of bug that might try to create records like (33, Mike, Math), and (33, Jerry, English), where suddenly the correct Name value for an ID is ambiguous. — Joel Coehoorn
– Joel Coehoorn, Commented Jan 2, 2021 at 2:31
@asmgx this is a clear duplicate, use any of the solutions in the other issue for each row in your query. We need to keep this knowledge in the one place to help future users. — Chris Schaller
– Chris Schaller, Commented Jan 2, 2021 at 3:20

John Cappelletti · Accepted Answer · 2021-01-02 02:18:52Z

3

Just another option using a little XML

Example

Declare @YourTable Table ([ID] varchar(50),[Name] varchar(50),[Subjects] varchar(50))
Insert Into @YourTable Values 
 (33,'Mike','Math,English,Physics')
,(24,'Paul','Art,French,Med,English,Math')
,(58,'Sami','Physics,Biology')
,(22,'Nora','Math,English,Art')
,(76,'Mona','Math,English,French,Med,Physics')
,(39,'Lila','Physics')
,(19,'Dave','Math,Biology,Physics')
,(48,'Jade','English,French,Physics')
,(82,'Mark','Med,Biology,Physics')
,(23,'Nina','Biology,English,Physics')
 
Select A.ID
      ,A.Name
      ,B.* 
 From @YourTable A
 Cross Apply (
                Select RetSeq = row_number() over (order by 1/0)
                      ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  ( values (cast('<x>' + replace((Select replace([Subjects],',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.'))) as A(x)
                Cross Apply x.nodes('x') AS B(i)

             ) B

Returns

ID  Name    RetSeq  RetVal
33  Mike    1       Math
33  Mike    2       English
33  Mike    3       Physics
24  Paul    1       Art
24  Paul    2       French
24  Paul    3       Med
24  Paul    4       English
24  Paul    5       Math
58  Sami    1       Physics
58  Sami    2       Biology
22  Nora    1       Math
22  Nora    2       English
22  Nora    3       Art
76  Mona    1       Math
76  Mona    2       English
76  Mona    3       French
76  Mona    4       Med
76  Mona    5       Physics
39  Lila    1       Physics
19  Dave    1       Math
19  Dave    2       Biology
19  Dave    3       Physics
48  Jade    1       English
48  Jade    2       French
48  Jade    3       Physics
82  Mark    1       Med
82  Mark    2       Biology
82  Mark    3       Physics
23  Nina    1       Biology
23  Nina    2       English
23  Nina    3       Physics

answered Jan 2, 2021 at 2:18

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Yitzhak Khabinsky Over a year ago

What is the reason to use the '§§Split§§'. It is causing a double REPLACE() function call.

John Cappelletti Over a year ago

@YitzhakKhabinsky Just a UNIQUE and XML Safe key. Can be any rare string.

Yitzhak Khabinsky Over a year ago

Thanks for the explanation. I usually use a CDATA section for that and one REPLACE() function call..

John Cappelletti Over a year ago

@YitzhakKhabinsky FYI - If 2016+ ... the JSON approach is a nudge more performant

Yitzhak Khabinsky Over a year ago

Please up-vote: feedback.azure.com/forums/908035-sql-server/suggestions/…

Gordon Linoff · Accepted Answer · 2021-01-02 02:59:37Z

1

You can use string_split() in the most recent versions of SQL Server:

SELECT ID, Name, ss.value as subject
FROM Students s CROSS APPLY
     string_split(s.subjects, ',') ss;

You can also play with JSON or define your own split() function, although in older versions, I would just use a recursive CTE:

with cte as (
      select s.id, convert(varchar(max), null) as subject, convert(varchar(max), subjects + ',') as rest
      from students s
      union all
      select id, left(rest, charindex(',', rest) - 1),
             stuff(rest, 1, charindex(',', rest), '')
      from cte
      where rest <> ''
     )
select *
from cte
where subject is not null;

Here is a db<>fiddle.

edited Jan 2, 2021 at 2:59

answered Jan 2, 2021 at 2:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

asmgx Over a year ago

Your first answer gives these errors: The multi-part identifier "s.Subjects" could not be bound. Argument data type void type is invalid for argument 1 of string_split function. The correlation name 's' is specified multiple times in a FROM clause.

Chris Schaller Over a year ago

OP already pointed out that string_split() doesn't work, but this output does not match OP's expectation, after putting this much effort into it you should make sure the output is the same as OP requires, otherwise OP is just as clueless after reading the dup origin answers

Gordon Linoff Over a year ago

@ChrisSchaller . . . The OP does not use string_split() correctly in the query in the question.

Collectives™ on Stack Overflow

Save a comma separated array in a string in a table [duplicate]

Response to suggested duplicate

2 Answers 2

5 Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Response to suggested duplicate

2 Answers 2

5 Comments

3 Comments

Linked

Related