Multiple rows based on column with multiple values

Question

I have a table that has multiple values in a column. I need to create individual rows for each value. I have no clue how to even approach that. I can split the values in the column by comma but after that I'm lost. Any suggestion is welcome.

Table Courses

    Name | Courses
--------------------------
    Kyle | Math, English, Science
    Joe  | Math, English

Resulting Table

Name | Courses
-----------------------------
Kyle | Math
Kyle | English
Kyle | Science
Joe  | Math
Joe  | English

Ideally, this is not the way you would model this in the database. You'd have a student table, a course table and a many-to-many link table to link the two together. — MikeS, Jul 18 '19 at 19:54

score 0 · Answer 1 · answered Jul 18 '19 at 19:10

Try this-

Help taken from - Here

;WITH CTE (Name,Courses)
AS
(
    SELECT Name,Courses FROM Courses
),
tmp(Name, DataItem, Courses) 
AS
(
    SELECT
        Name,
        CAST(LEFT(Courses, CHARINDEX(',', Courses + ',') - 1) AS VARCHAR),
        STUFF(Courses, 1, CHARINDEX(',', Courses + ','), '')
    FROM CTE

    UNION all

    SELECT
        Name,
        CAST(LEFT(Courses, CHARINDEX(',', Courses + ',') - 1) AS VARCHAR),
        STUFF(Courses, 1, CHARINDEX(',', Courses + ','), '')
    FROM tmp
    WHERE
        Courses > ''
)

SELECT
    Name,LTRIM(RTRIM(DataItem)) Course
FROM tmp
ORDER BY Name

Multiple rows based on column with multiple values

1 Answers1