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.date, 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.date, t1.price 


 drop table Invoices, users

 
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