-7
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 
  • 2
    Please don't be disheartened by the down votes on this question. I hope you find the 'duplicate' useful to solve your issue. Searching the site for existing answers can save you time :) – Paul White 9 Aug 11 '15 at 2:38
0

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.