CODEWITHSUNDEEP
sqlsql-server-2008user-defined-functions
Ali Adravi
Ali Adravi

Reputation: 22783

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?

Upvotes: 0

Views: 2239

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

This should be something like this

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

Btw: OUTER APPLY would join all of them in any case...

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270463

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.

Upvotes: 1

Related Questions