immtrac
asked on
Split comma sperated values to multiple records
I have a table which has columns like: (actual no of columns in table are 15)
P_ID, C_ID, PR_ID, data
-------------------------- ---------- ---------- ---
1 2 3 a,b,c,d,f,
2 3 4 NULL
5 6 3 L,M,
what I want is create a view which gives me record for each comma separated value, something like this:
1 2 3 a
1 2 3 b
1 2 3 c
1 2 3 d
1 2 3 f
2 3 4 NULL
5 6 3 L
5 6 3 M
I am using SQL 2008. And also have a UDF split function which is
dbo.Split('x,y,z,w',',') and returns a table like
Id Value
1 x
2 y
3 z
4 w
I would like to make use of this function if possible.
Thanks for your help
P_ID, C_ID, PR_ID, data
--------------------------
1 2 3 a,b,c,d,f,
2 3 4 NULL
5 6 3 L,M,
what I want is create a view which gives me record for each comma separated value, something like this:
1 2 3 a
1 2 3 b
1 2 3 c
1 2 3 d
1 2 3 f
2 3 4 NULL
5 6 3 L
5 6 3 M
I am using SQL 2008. And also have a UDF split function which is
dbo.Split('x,y,z,w',',') and returns a table like
Id Value
1 x
2 y
3 z
4 w
I would like to make use of this function if possible.
Thanks for your help
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Simple and it worked for me
To insure that the split values list in order, add an ORDER BY:
SELECT
tn._ID, tn.C_ID, tn.PR_ID, --...
d.value
FROM dbo.table_name tn
CROSS APPLY dbo.Split(data, ',') AS d
ORDER BY
tn._ID,
d.Id
SELECT
tn._ID, tn.C_ID, tn.PR_ID, --...
d.value
FROM dbo.table_name tn
CROSS APPLY dbo.Split(data, ',') AS d
ORDER BY
tn._ID,
d.Id
Open in new window