Split string from execute result

Question

I have result when executing my query like that:

+--------+--------+
-  col1  -  col2  -
+--------+--------+
-  a,b   -   1    -
-  a,c   -   2    -
-  b,c   -   3    -
- a,b,c  -   4    -
- a,c,d  -   5    -
+--------+--------+

How I can split each result row into new row result with only "a" or "b" or "c"

+--------+--------+
-  col1  -  col2  -
+--------+--------+
-  a     -   1    -
-  a     -   2    -
-  a     -   4    -
-  a     -   5    -
-  b     -   1    -
-  b     -   3    -
-  b     -   4    -
-  c     -   2    -
-  c     -   3    -
-  c     -   4    -
-  c     -   5    -
-  d     -   5    -
+--------+--------+

search for string split function like `Jeff Moden's` `DelimitedSplit8k` and use that — Squirrel, May 05 '20 at 02:41
Read [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** — Zohar Peled, May 05 '20 at 08:26

score 1 · Accepted Answer · answered May 05 '20 at 02:23

1

You can use string_split():

select s.value, t.col2
from t cross apply
     string_split(col1, ',') s

answered May 05 '20 at 02:23

Gordon Linoff

1,242,037
58
646
786

1

Thank you for your help. It's work with SQL Server 20'16. – Duy Khương May 05 '20 at 02:31

Split string from execute result

1 Answers1