CODEWITHSUNDEEP
sqlsql-server
PacoDePaco
PacoDePaco

Reputation: 699

Select split values with their matching ids

So I have a following table:

id (int)  | val (varchar)
------------------------------------
1         | 4234,15345,1324,1534,1512
2         | 215,22867,2456,24756,2423
3         | 3123,3452,3356,3478,3995

Also, I have a function fnSplitString (string varchar(max), deliminator char(1)), which works like this:

 SELECT * FROM fnSplitString ((SELECT val FROM idval WHERE id = 1),',')

produces output:

    val (varchar)
------------
    4234
    15345
    1324
    1534
    1512

My goal is to produce a table like this:

id (int)  val (varchar)
    ------------
 1       4234
 1       15345
 1       1324
 1       1534
 1       1512
 2       215
 2       22867
 2       2456
 ...

I know how to achieve it using a cursor and inserting into a temporary table. I am just curious if this could be done without a cursor though? Let's assume I don't want to modify the fnSplitString function at first. So far I've come up with one part of the query:

SELECT id, spl.val 
FROM idval JOIN 
(SELECT * FROM fnSplitString ((SELECT val FROM idval WHERE id = 1),',')) spl 
ON 1=1 
WHERE id = 1

Which leaves me with the result table only for rows with id = 1.

Upvotes: 1

Views: 511

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You have to use CROSS APPLY:

SELECT *
FROM idval i
CROSS APPLY dbo.fnSplitString(i.val, ',') f

Upvotes: 3

Related Questions