How to Concatenate Fields from Multiple T-SQL Records into a Comma-Separated String?

Group fields of multiple records by another field in T-SQL

This article will explore a technique for grouping field values ​​associated with multiple records in two tables. By combining data from the Reviews, Reviewers, and Users tables, we can concatenate the FName fields into a comma-separated string.

<code class="language-sql">-- 示例数据
DECLARE @Reviews TABLE(
        ReviewID INT,
        ReviewDate DATETIME
)

DECLARE @Reviewers TABLE(
        ReviewerID   INT,
        ReviewID   INT,
        UserID INT
)

DECLARE @Users TABLE(
        UserID  INT,
        FName  VARCHAR(50),
        LName VARCHAR(50)
)

INSERT INTO @Reviews SELECT 1, '12 Jan 2009'
INSERT INTO @Reviews SELECT 2, '25 Jan 2009'

INSERT INTO @Users SELECT 1, 'Bob', ''
INSERT INTO @Users SELECT 2, 'Joe', ''
INSERT INTO @Users SELECT 3, 'Frank', ''
INSERT INTO @Users SELECT 4, 'Sue', ''
INSERT INTO @Users SELECT 5, 'Alice', ''

INSERT INTO @Reviewers SELECT 1, 1, 1
INSERT INTO @Reviewers SELECT 2, 1, 2
INSERT INTO @Reviewers SELECT 3, 1, 3
INSERT INTO @Reviewers SELECT 4, 2, 4
INSERT INTO @Reviewers SELECT 5, 2, 5</code>

Solution using nested SELECT and FOR XML PATH()

Using the FOR XML PATH() operator, we can concatenate the FName values ​​into a single string:

<code class="language-sql">SELECT  *,
        ( 
            SELECT  u.FName + ','
            FROM    @Users u INNER JOIN 
                    @Reviewers rs ON u.UserID = rs.UserID
            WHERE   rs.ReviewID = r.ReviewID
            FOR XML PATH('')
        ) AS UserNames
FROM    @Reviews r</code>

This query returns the following results:

<code>ReviewID  ReviewDate  UserNames
1          12 Jan 2009  Bob,Joe,Frank
2          25 Jan 2009  Sue,Alice</code>

Conclusion

Using the FOR XML PATH() function, we successfully grouped the field values ​​of multiple records in different tables by a common field. This technique can be applied to scenarios where concatenated string representations are required.

The above is the detailed content of How to Concatenate Fields from Multiple T-SQL Records into a Comma-Separated String?. For more information, please follow other related articles on the PHP Chinese website!