Save a comma separated array in a string in a table

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, Jan 02 '21 at 01:51
No it's not. For earlier versions of SQL Server, you solve it with a table valued function. — Jonathan Willcock, Jan 02 '21 at 01: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, Jan 02 '21 at 01: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, Jan 02 '21 at 02: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, Jan 02 '21 at 03:20
@ChrisSchaller This is not the same question.. can you please point where is the answer that can resolve the problem that I have?? Is there any answer that is similar to the one Gordon proposed?? — asmgx, Jan 02 '21 at 04:28
Any of the responses that involves creating a function would have worked, your usage of `string_split` was just wrong, because it returns a table, you have to use CROSS APPLY to merge the results, as Gordon has explained. In the end your question, to not be a duplication should have asked _how to return the results from a table valued function that operates on a column in my query_ or something like that — Chris Schaller, Jan 02 '21 at 06:18
@ChrisSchaller I know string_split is returning table and my question is about how to use it when the table is in every record in another table.. the trick is in CROSS APPLY and that is what I was looking for.. the other question is a simple like which a simple FROM string_split() can work for... I got the answer thanks to Gordon.. thought it might help other with the same issue — asmgx, Jan 02 '21 at 06:24
That's the point, your question was ambigous, was the issue the fact you were using an older version of SQL, or was it just you were having trouble using a table valued function. So you need to be clear in your question and the title on what your actual issue is and then we will reopen it. — Chris Schaller, Jan 02 '21 at 06:27
Response to response to closure. Please re-read your title and question. How will anyone searching in the future know that your real issue was not knowing how to combine a table valued function with a `SELECT`? I quote How can I split these subjects into rows? For older versions of SQL Server the answer is via a table valued function, and as such your question is a clear duplicate. If you want to edit your question (and title) to make it clear that you knew how to split strings but didn't know how to use a TVF, then I will vote to re-open. — Jonathan Willcock, Jan 03 '21 at 19:10

score 3 · Answer 1 · answered Jan 02 '21 at 02:18

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

What is the reason to use the **'§§Split§§'**. It is causing a double REPLACE() function call. — Yitzhak Khabinsky, Jan 03 '21 at 00:57
@YitzhakKhabinsky Just a UNIQUE and XML Safe key. Can be any rare string. — John Cappelletti, Jan 03 '21 at 01:00
Thanks for the explanation. I usually use a CDATA section for that and one REPLACE() function call.. — Yitzhak Khabinsky, Jan 03 '21 at 01:02
@YitzhakKhabinsky FYI - If 2016+ ... the JSON approach is a nudge more performant — John Cappelletti, Jan 03 '21 at 01:05
Please up-vote: https://feedback.azure.com/forums/908035-sql-server/suggestions/38142115-sql-server-vnext-post-2019-and-nosql-functionali — Yitzhak Khabinsky, Jan 03 '21 at 01:07

Gordon Linoff · Accepted Answer · 2021-01-02T02:59:37.123

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 02 '21 at 02:59

answered Jan 02 '21 at 02:11

Gordon Linoff

1,242,037
58
646
786

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. – asmgx Jan 02 '21 at 02:54
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 – Chris Schaller Jan 02 '21 at 06:02
@ChrisSchaller . . . The OP does not use `string_split()` correctly in the query in the question. – Gordon Linoff Jan 02 '21 at 18:37

Save a comma separated array in a string in a table

Response to suggested duplicate

2 Answers2