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) = ',' )

as (Select m.ID,m.TB1_ID,t.Name from mycte m join TB1 t on m.splitID=t.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

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s