Split comma separated value into columns in sql server

Question

I have below table

Id     Name      member
1,2,3  Ab,Xy,Pq  member1
8,9    De,Fg     member2

I want a temp table like below in sql server 2008

Id  Name  member
1   Ab    member1
2   Xy    member1
3   Pq    member1
8   De    member2
9   Fg    member1

Edit : Please check my table is having two column with comma separated values and for those values i need result shown in last table. 1 with Ab, 2 with Xy and so on.

Answer 1

try the below query

    declare @t table (id varchar(20),name varchar(20),member varchar(20))
    insert into @t (id,Name,member) values 
    ('1,2,3',  'Ab,Xy,Pq',  'member1'),
    ('8,9' ,   'De,Fg'  ,   'member2')


;with t as (select row_number() over (order by member) r, t.member,a.b.value('.','varchar(10)') n from
    (select member, cast('<t>'+ replace(id,',','</t><t>')+'</t>' as xml) as  id,cast('<t>'+ replace(name,',','</t><t>')+'</t>' as xml) as nme from @t) t
    cross apply id.nodes('/t') a(b) ),
t1 as (select row_number() over (order by member) r,a.b.value('.','varchar(10)') n1 from 
    (select member, cast('<t>'+ replace(id,',','</t><t>')+'</t>' as xml) as  id,cast('<t>'+ replace(name,',','</t><t>')+'</t>' as xml) as nme from @t) t
    cross apply nme.nodes('/t') a(b))

select t.member,t.n,t1.n1 from t inner join t1 on t.r=t1.r