Home > Point2 - Technical > MS SqlServer Row Value Concatenation

MS SqlServer Row Value Concatenation

November 6, 2009

Recently, a colleague and I found ourselves in the situation of needing to concatenate values from multiple rows of a MS SqlServer database table. We were trying to form a comma-delimited list of phone numbers for every customer in our database. One customer may have many phone numbers. Here is a simplified diagram visualizing the relationship between customer and phone numbers:

customer_phonenumber

Customer - Phone Number Relationship

The format of data we were looking for was:

CustomerId PhoneNumbers
1 “(306) 555-1111”, “555-2222”, “306-555-3333”
2 “3065554444”

We tried various queries to perform the concatenation correctly but none of our solutions seemed to do the job perfectly; either the final ‘PhoneNumbers’ string would have an additional comma at the end or some other undesired effect. We finally came across an article explaining in great detail of how to use TSQL (the SQL engine behind SqlServer) to perform the operations we needed.

http://www.projectdmx.com/tsql/rowconcatenate.aspx

This article has various examples describing AND explaining how to do multiple row value concatenations such as explicit examples for “Concatenating values when the number of items is small and known upfront” and “Concatenating values when the number of items is not known”. The author even walks through a recursive solution or two.

We determined that we knew none of our customers had more than five phone numbers based on the fact that they were limited to the PhoneType enumeration which only has five values. This allowed us to use the articles first example to get our job done efficiently. Here is our final solution:

SELECT CustomertId, REPLACE(
 '"' + MAX( CASE seq WHEN 1 THEN phoneNumber ELSE '' END ) + '",' +
 '"' + MAX( CASE seq WHEN 2 THEN phoneNumber ELSE '' END ) + '",' +
 '"' + MAX( CASE seq WHEN 3 THEN phoneNumber ELSE '' END ) + '",' +
 '"' + MAX( CASE seq WHEN 4 THEN phoneNumber ELSE '' END ) + '",' +
 '"' + MAX( CASE seq WHEN 5 THEN phoneNumber ELSE '' END ) + '"', ',""', '' ) as PhoneNumbers
FROM (
 SELECT pn1.CustomerId, pn1.PhoneNumber, (
 SELECT COUNT(*)
 FROM PhoneNumber pn2
 WHERE pn2.CustomerId = pn1.CustomerId
 AND pn2.phoneNumber <= pn1.phoneNumber)
 FROM PhoneNumber pn1) PhoneNumbersPerParty ( CustomerId, phoneNumber, seq )
GROUP BY CustomerId
By: Jesse Webb
Advertisements
%d bloggers like this: