0

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.

Arpan Paliwal
  • 234
  • 1
  • 7
  • 20
  • 3
    Possible duplicate of [Turning a Comma Separated string into individual rows](http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) – legoscia Nov 25 '15 at 13:06
  • And so far, you've tried...? – LDMJoe Nov 25 '15 at 13:14
  • The solution given in that post is --> ;with tmp(SomeID, OtherID, DataItem, Data) as ( select SomeID, OtherID, LEFT(Data, CHARINDEX(',',Data+',')-1), STUFF(Data, 1, CHARINDEX(',',Data+','), '') from Testdata union all select SomeID, OtherID, LEFT(Data, CHARINDEX(',',Data+',')-1), STUFF(Data, 1, CHARINDEX(',',Data+','), '') from tmp where Data > '' ) I need a temporary table which persist till i am connected to sql server. Is it creating temporary table? I am not familiar with the SQL. – Arpan Paliwal Nov 25 '15 at 13:36

1 Answers1

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
nazark
  • 1,240
  • 2
  • 10
  • 15