MS SqlServer Row Value Concatenation
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:
The format of data we were looking for was:
|1||“(306) 555-1111”, “555-2222”, “306-555-3333”|
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.
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