Combining table column in SQL Server

January 10, 2022

I have to combine the Customer_address when they have the same address_index but different line_nbr:

CUSTOMER_NUM	CUSTOMER_CODE	CUSTOMER_ADDRSS	LINE_NBR	ADDRESS_INDEX	ADDRESS_CODE
31	GEW	3901 Castle Hayne Road	17	1	SHIP
30	GEW	Highway 117 N	18	1	SHIP
212	GEW	1005 2nd Street	19	2	SHIP
23	GEW	3901 Castle Hayne Road	20	3	SHIP
241	GEW	Highway 117 N	21	3	SHIP
360	GEW	465 McCarthy Road	1	4	SHIP
469	GEW	3901 Castle Hayne Road	1	5	SHIP
470	GEW	Highway 117 N	2	5	SHIP

I need to combine CUSTOMER_NUM 31 and 30 to read

Castle Hayne Road Highway 117 N

By combining Address_index = 1 with Line_nbr 17 and 18, and also Address_index = 3 with line_nbr 20 and 21, and Address_index 5 with line_nbr 1 and 2, etc….

New to SQL and trying to figure out how to link the two columns together and write out the customer address in one column instead of the two rows it currently resides.

Thanks!

>Solution :

On SQL Server 2017+, STRING_AGG() makes this easy:

SELECT CUSTOMER_CODE, 
       MergedAddress = STRING_AGG(CUSTOMER_ADDRSS, ' ') 
                       WITHIN GROUP (ORDER BY LINE_NBR)
  FROM dbo.MyTable
  GROUP BY CUSTOMER_CODE, ADDRESS_INDEX;

Output:

CUSTOMER_CODE	MergedAddress
GEW	3901 Castle Hayne Road Highway 117 N
GEW	1005 2nd Street
GEW	3901 Castle Hayne Road Highway 117 N
GEW	465 McCarthy Road
GEW	3901 Castle Hayne Road Highway 117 N

On older versions, you’re going to have to use more cumbersome and inefficient methods like this:

SELECT CUSTOMER_CODE, 
       MergedAddress = RTRIM((SELECT CUSTOMER_ADDRSS + ' '
         FROM dbo.MyTable AS inside
         WHERE inside.ADDRESS_INDEX = outside.ADDRESS_INDEX
         ORDER BY LINE_NBR
         FOR XML PATH(''), TYPE).value(N'./text()[1]', N'varchar(max)'))
  FROM dbo.MyTable AS outside 
  GROUP BY CUSTOMER_CODE, ADDRESS_INDEX;

The example db<>fiddle shows the results and also the plans that should help explain why the latter is so bad.