FOR XML PATH('') を使用して複数の行をコンマ区切りのリストに結合する

2024-04-07

SQL Server 2005で複数の行をコンマ区切りのリストに結合する方法

SQL Server 2005で、複数の行のデータを1つの行に結合し、コンマで区切りのリストを作成したい。

解決策：

SQL Server 2005では、以下の3つの方法で複数の行をコンマ区切りのリストに結合できます。

FOR XML PATH('')

SELECT
  (
    SELECT
      column_name
    FROM
      table_name
    FOR XML PATH('')
  )
FROM
  table_name;

STUFF

SELECT
  STUFF(
    (
      SELECT
        ',' + column_name
      FROM
        table_name
      FOR XML PATH('')
    ),
    1,
    1,
    ''
  )
FROM
  table_name;

STRING_AGG

SELECT
  STRING_AGG(column_name, ',')
FROM
  table_name;

各方法の詳細とメリット・デメリット

メリット:

比較的シンプルな方法

古い方法であり、非推奨

少し複雑な構文

最も新しい方法であり、最も効率的

SQL Server 2005 では STRING_AGG はサポートされていない

補足

上記の例では、column_name は結合したい列名に置き換えてください。
結合する列にNULL値が含まれる場合、NULL値は無視されます。
出力されるリストの順序を制御したい場合は、ORDER BY句を使用できます。

上記以外にも、いくつかの方法で複数の行を結合できます。詳細は、SQL Server のドキュメントを参照してください。

-- FOR XML PATH('')

SELECT
  (
    SELECT
      FirstName
    FROM
      Customers
    FOR XML PATH('')
  )
FROM
  Customers;

-- STUFF

SELECT
  STUFF(
    (
      SELECT
        ',' + FirstName
      FROM
        Customers
      FOR XML PATH('')
    ),
    1,
    1,
    ''
  )
FROM
  Customers;

-- STRING_AGG

SELECT
  STRING_AGG(FirstName, ',')
FROM
  Customers;

出力例

John,Jane,Joe

他の方法

GROUP BY

SELECT
  GROUP_CONCAT(FirstName, ',')
FROM
  Customers
GROUP BY
  CustomerID;

複数の列を結合できる

PIVOT

SELECT
  FirstName
FROM
  (
    SELECT
      FirstName,
      ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY FirstName) AS rn
    FROM
      Customers
  ) AS t
PIVOT
(
  MAX(FirstName)
  FOR rn IN ([1],[2],[3])
) AS p;

複雑な結合処理が可能

構文が複雑

自作関数

CREATE FUNCTION dbo.fn_Concat
(
  @input VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
  DECLARE @output VARCHAR(MAX)

  SET @output = ''

  WHILE LEN(@input) > 0
  BEGIN
    SET @output = @output + SUBSTRING(@input, 1, CHARINDEX(',', @input)) + ','

    SET @input = SUBSTRING(@input, CHARINDEX(',', @input) + 1, LEN(@input))
  END

  SET @output = LEFT(@output, LEN(@output) - 1)

  RETURN @output
END;

SELECT
  dbo.fn_Concat(FirstName)
FROM
  Customers;