[解決済み] SQL Server で複数行のテキストを 1 つのテキスト文字列に連結する方法

質問

名前を保持するデータベースのテーブルが、3行あるとする。

Peter
Paul
Mary

の1つの文字列にする簡単な方法はありますか？ Peter, Paul, Mary ?

解決方法は？

SQL Server 2017またはAzureをお使いの場合は Mathieu Rendaの回答 .

一対多の関係を持つ2つのテーブルを結合しようとしたとき、同じような問題が発生しました。SQL 2005 では、次のようなことがわかりました。 XML PATH メソッドは、行の連結を非常に簡単に処理することができます。

というテーブルがある場合 STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

期待した結果は

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

私は以下を使用しました。 T-SQL :

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH (''), TYPE
            ).value('text()[1]','nvarchar(max)') [Students]
        FROM dbo.Students ST2
    ) [Main]

同じことをよりコンパクトに行うには、冒頭のカンマを連結して substring をスキップして、サブクエリを実行する必要がないようにします。

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2