Replace id value with name for a not normalized table

create table Invoices (Invoiceid int, date date, price int,userid varchar(50))
insert into Invoices values(1,'1/1/2015',35,'|5|6|9|'),(2,'3/4/2015',25,'|5|10|')

 create table  Users (userid int, userName varchar(50))
 Insert into Users values(5,'John'),(6,'Mike'),(9,'Tom'),(10,'Sara')

 --Select * from invoices

 ;With mycte as (
 select Invoiceid, date, price,  substring(userid, n, charindex('|', userid + '|', n) - n)  userid
from invoices
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(userid) AND substring('|' + userid, n, 1) = '|'
 ,mycte1 as (
 Select Invoiceid, date, price,username from mycte m join  users u on m.userid=u.userid

 SELECT t1.Invoiceid,, t1.price,
       Stuff(( SELECT ';' + Cast(t2.username as varchar(5))
           FROM mycte1 t2
          WHERE t2.Invoiceid = t1.Invoiceid 
		  ANd username<>''
          ORDER BY t2.username
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS ids
  FROM mycte1 t1
 GROUP BY t1.Invoiceid ,, t1.price 

 drop table Invoices, users


Leave a Reply

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

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