Concatenating Rows into One Cell and Adding Break Line after Each Row using SQL Server

Introduction

In this article, we will explore how to concatenate rows of data from multiple tables into one cell in SQL Server. We will also discuss how to add a break line (newline) after each concatenated row.

Background

SQL Server 2017 introduced the STRING_AGG function, which allows us to concatenate strings together using a specified separator. However, this function requires that all input columns have the same data type and length. In some cases, we may want to concatenate rows from multiple tables into one cell, but without having the same column structure.

The Challenge

The original query uses the STUFF function to concatenate strings together. However, this approach has a few limitations:

• It requires that all input columns have the same data type and length.
• It can be cumbersome to handle multiple tables and joins.
• It does not provide an easy way to add a break line (newline) after each concatenated row.

Solution 1: Using STRING_AGG

One approach to solve this problem is to use the STRING_AGG function. This function allows us to concatenate strings together using a specified separator, and it is more flexible than the STUFF function.

To use STRING_AGG, we need to join all the tables that contain the data we want to concatenate, and then group the results by the required columns.

Here is an example query:

SELECT 
    p.PersonID,
    STRING_AGG( 
        l.Language + '(' + 
            CASE cvnl.Proficiency 
                WHEN 1 THEN 'Good'
                WHEN 2 THEN 'Very Good'
                WHEN 3 THEN 'Excellent'
            END + ')'
        , CHAR(13) + CHAR(10)) AS Languages
FROM CV_Certifications cv
JOIN person p on cv.PersonID = p.PersonID
JOIN CV_NewLanguages cvnl on cvnl.PersonID = p.PersonID
JOIN Languages l on l.LanguageID = cvnl.LanguageID
WHERE active=1      
GROUP BY p.PersonID,
ORDER BY p.PersonID

This query joins the CV_Certifications, person, CV_NewLanguages, and Languages tables, groups the results by PersonID, and concatenates the Language columns together using a separator.

To add a break line after each concatenated row, we can modify the separator to include an HTML break tag (<BR>) like this:

SELECT 
    p.PersonID,
    STRING_AGG( 
        l.Language + '(' + 
            CASE cvnl.Proficiency 
                WHEN 1 THEN 'Good'
                WHEN 2 THEN 'Very Good'
                WHEN 3 THEN 'Excellent'
            END + ')'
        , CHAR(13) + CHAR(10) + '<BR>' + CHAR(13) + CHAR(10)) AS Languages
FROM CV_Certifications cv
JOIN person p on cv.PersonID = p.PersonID
JOIN CV_NewLanguages cvnl on cvnl.PersonID = p.PersonID
JOIN Languages l on l.LanguageID = cvnl.LanguageID
WHERE active=1      
GROUP BY p.PersonID,
ORDER BY p.PersonID

Solution 2: Using STUFF

If we cannot use STRING_AGG for some reason, we can try to modify the original query using STUFF. This approach is more complex and requires that we handle multiple tables and joins.

To use STUFF, we need to join all the tables that contain the data we want to concatenate, and then replace the comma in the STUFF function with a separator that includes an HTML break tag (<BR>).

Here is an example query:

SELECT 
    p.PersonID,
    REPLACE(
        STUFF( (
            SELECT ',' + l.Language +' (' + 
            CASE cvnl.Proficiency 
                WHEN 1 THEN 'Good'
                WHEN 2 THEN 'Very Good'
                WHEN 3 THEN 'Excellent'
            END +') ' 
            FROM CV_NewLanguages cvnl
            JOIN Languages l on l.LanguageID = cvnl.LanguageID
            WHERE cvnl.PersonID = p.PersonID
            ORDER BY l.Language ASC 
            FOR XML PATH(''))
         , 1, 1, ''),
        ',', CHAR(13) + CHAR(10) + '<BR>' + CHAR(13) + CHAR(10)) AS Languages
FROM person p  
WHERE EXISTS (SELECT 1 FROM CV_Certifications cv WHERE cv.PersonID = p.PersonID)
AND EXISTS (SELECT 1 FROM CV_NewLanguages cvnl WHERE cvnl.PersonID = p PersonID
AND active=1      
ORDER BY p.PersonID

Conclusion

Concatenating rows of data from multiple tables into one cell in SQL Server can be a challenging task. However, using the STRING_AGG function provides a flexible and efficient solution that is easier to maintain than modifying the original query with STUFF. By adding an HTML break tag (<BR>) to the separator, we can easily add a break line after each concatenated row.

Last modified on 2023-09-17