Select split values with their matching ids

Question

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.

Don't store data like comma separated items (in a column), it will only cause you lots of problems. Normalize! One value/row is the SQL way! — jarlh, Apr 27 '16 at 07:52
Yep, no choice when importing data from our clients. Thanks for your advice anyway ;] — PacoDePaco, Apr 27 '16 at 07:56
You can even split a CSV without using a UDF -fnSplitString . http://stackoverflow.com/a/36686492/5366015 — Abdul Rasheed, Apr 27 '16 at 08:00

score 3 · Accepted Answer · answered Apr 27 '16 at 07:50

3

You have to use CROSS APPLY:

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

answered Apr 27 '16 at 07:50

Felix Pamittan

31,544
7
41
67

1

I knew it'll be simple but not that much ;] I just need to get rid of the duplicate val column here. Thanks! – PacoDePaco Apr 27 '16 at 07:57

Select split values with their matching ids

1 Answers1