Split and Combine Values (T-SQL)


 
 create table TB1 (ID int, Name varchar(10))
Insert TB1 values(1,'Prakash'),(2,'Durga')
create table TB2 (ID int, TB1_ID varchar(10))
Insert TB2 values(1,'1,2')
 

 
 
;with mycte as (
select ID, TB1_ID,  substring(TB1_ID, n, charindex(',', TB1_ID + ',', n) - n)  splitID
from TB2
cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)) d(n) 
Where n <= len(TB1_ID) AND substring(',' + TB1_ID, n, 1) = ',' )


,mycte1 
as (Select m.ID,m.TB1_ID,t.Name from mycte m join TB1 t on m.splitID=t.ID)


SELECT t1.ID,t1.TB1_ID,
       Stuff(( SELECT ',' + Cast(t2.Name as varchar(5))
           FROM mycte1 t2
          WHERE t2.ID = t1.ID  
          ORDER BY id
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS Names
  FROM mycte1 t1
 GROUP BY t1.ID,t1.TB1_ID;



 

drop table TB1,TB2
 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s