SQLTeam.com | Weblogs | Forums

Append data to end of record


I'm working on importing customers into a new system.

in the old software Alternate ship to addresses were kept in a different table. In the new software alternate ship to's seem to be added to the end of the customer record.

I have one customer that has over 400 different ship to locations.

Is there a way to write a select statement that would append each ship to address to the end of a customer record?

cus_name ship_to_1, ship_to_2, ship_to_3.......


Something like this? I delimited addresses with |

drop table if exists #Customers
drop table if exists #CustomerAddresses

Create table #Customers 
	(CustomerID int,
	 CustomerName varchar(30))

Create table #CustomerAddresses 
	(CustomerID int,
	 ShipToAddress varchar(100)

Insert into #Customers values 
(1, '1 ShipToAddress'),
(2, '10 ShipToAddress'),
(3, '3 ShipToAddress'),
(4, '1 ShipToAddress')

insert into #CustomerAddresses values 
(1, 'ShipToAddress'),
(2, '2_1_ShipToAddress'),
(2, '2_2_ShipToAddress'),
(2, '2_3_ShipToAddress'),
(2, '2_4_ShipToAddress'),
(2, '2_5_ShipToAddress'),
(2, '2_6_ShipToAddress'),
(2, '2_7_ShipToAddress'),
(2, '2_8_ShipToAddress'),
(2, '2_9_ShipToAddress'),
(2, '2_10_ShipToAddress'),
(3, '3_1_ShipToAddress'),
(3, '3_2_ShipToAddress'),
(3, '3_3_ShipToAddress'),
(4, '4_1_ShipToAddress')

select c.CustomerName ,
        (       SELECT '|' + ShipToAddress  
                FROM #CustomerAddresses t2  
                WHERE c.CustomerID=t2.CustomerID  
                FOR XML PATH('') 
      ) AS t 
 from #customers c

GROUP BY c.CustomerName, c.CustomerID