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 ,
STUFF(
( SELECT '|' + ShipToAddress
FROM #CustomerAddresses t2
WHERE c.CustomerID=t2.CustomerID
FOR XML PATH('')
),
1,
1,''
) AS t
from #customers c
GROUP BY c.CustomerName, c.CustomerID