Combining table column in SQL Server

user_15565452 0
Viewed 38

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.

sql-server-2017 sql-server
1 Answers

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.

user_613050
Related