Split after comma into separate row

Question

id name books
1  dan  history,physics,english
2  ron  chem,social,biology
3  mon  it,ece,eee

I need an output as

id name books
1  dan  history
1  dan  physics
1  dan  english
2  ron  chem
2  ron  social
2  ron  biology
3  mon  it
3  mon  ece
3  mon  eee 

Answer 1

Try this CTE. You should also read some tutorials about recursive CTE.

declare @t table(id int, name varchar(10), books nvarchar(100))

insert into @t(id, name, books) values
(1, 'dan', 'history,physics,english')
, (2, 'ron', 'chem,social,biology')
, (3, 'mon', 'it,ece,eee,www,zzz')

; With split(id, title, books) as (
    Select id, left(books, CHARINDEX(',', books)-1) 
        , right(books, LEN(books)-CHARINDEX(',', books)) 
    From @t
    Where books is not null and CHARINDEX(',', books) > 0
    Union All
    Select id, left(books, CHARINDEX(',', books)-1) as title
        , right(books, LEN(books)-CHARINDEX(',', books)) as books
    From split
    Where books is not null and CHARINDEX(',', books) > 0
    Union All
    Select id, books, null
    From split
    Where books is not null and CHARINDEX(',', books) = 0
)
Select s.id, t.name, s.title From split as s
Inner Join @t as t on t.id = s.id