Parsing a comma delimited string into multiple rows---Please Help!!!
-
Hi, I am on a new project and have absolutely no experience using SQL Server. This is the problem. I have a table with two columns(Employee ID, and Courses). Currently the courses are comma delimited in one field and I need to normalize the data in a new table. I found the code below to help get me started but I am still having trouble getting it to work correctly. Any assistance would be greatly appreciated.
Current Table Example:
Employee ID Courses
10 English, Calculus, Physics
20 Physics, Spanish
etc.
I want it to look as follows:
Employee ID Courses
10 English
10 Calculus
10 Physics
20 Physics
20 Spanish
I was told I need to write a stored procedure that has a cursor but I can not figure out how to make the cursor work with the code below. Thanks!!
Sample Code:
Create procedure sp_ParseArray
( @Array varchar(1000),
@separator char(1) )
AS
set nocount on
-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
declare @separator_position int -- This is used to locate each separator character
declare @array_value varchar(1000) -- this holds each array value as it is returned
-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
set @array = @array + @separator
-- Loop through the string searching for separtor characters
while patindex('%' + @separator + '%' , @array) <> 0
begin
-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @separator + '%' , @array)
select @array_value = left(@array, @separator_position - 1)
-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
select Array_Value = @array_value
-- This replaces what we just processed with and empty string
select @array = stuff(@array, 1, @separator_position, '')
end
set nocount off
go
-
SQL Server MVP Erland Sommarskog has posted a solution for this on his site..actually a couple of solutions:
http://www.sommarskog.se/arrays-in-sql.html#iterative
-
take a look at http://www.sqlservercentral.com/scripts/contributions/592.asp
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
If you have a table containing the list of courses you can do the following:
create table Course
(
CourseID integer,
CourseName varchar(255)
)
create Table StudentCourse
(
StudentID integer,
AllCoursesTaken varchar(1024)
)
insert course values(1, 'English')
insert course values(2, 'Calculus')
insert course values(3, 'Physics')
insert course values(4, 'Spanish')
insert studentCourse values(10, 'English, Calculus, Physics')
insert studentCourse values(20, 'Physics, Spanish')
SELECT s.StudentID, c.CourseName
FROM StudentCourse s, Course c
WHERE CHARINDEX(',' + Replace(CourseName, ' ', '') + ',', ',' + Replace(AllCoursesTaken, ' ', '') + ',') > 0
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply