Ali Adravi Ali Adravi - 24 days ago 7
SQL Question

Use join table column in split function in SQL Server 2008

I have a split function which is working well in SQL.

Users table has a column

experience
which contains comma-separated string as values, like this

ID | Exp
---------------------------
1 | C#,WEBAPI,SQL
2 | ASP.Net,MVC,HTML,CSS
3 | HTML,CSS,JavaScript


I am trying something:

Select
U.ID, B.Text
From
Users U,
Split(U.Exp, ',') B


Because I need result like:

ID | Exp
---------------------------
1 | C#
1 | WEBAPI
1 | SQL
2 | ASP.Net
2 | MVC
2 | HTML
2 | CSS
3 | HTML
3 | CSS
3 | JavaScript


Any clue how to use the join table in function in join?

Answer

You are looking for cross apply:

Select U.ID, B.Text
From Users U cross apply
     dbo.Split(U.Exp, ',') B(Text);

If you want to keep all rows in U even if U.Exp is empty, then you would use outer apply.