SQL Serverの文字列連結・集計をマスターしよう！サンプルコード付きでわかりやすく解説

SQL Server で文字列を連結・集計する最適な方法

文字列の連結

プラス演算子 (+)

最も単純な方法は、プラス演算子 (+) を使用する方法です。

SELECT
  CONCAT(column1, column2, column3) AS combined_column
FROM your_table;

この方法はシンプルで分かりやすいですが、以下の点に注意が必要です。

• 連結する文字列の数が 少ない場合 に適しています。
• 連結する文字列の データ型 が一致している必要があります。
• NULL 値を含む列を連結する場合、結果が NULL になる可能性があります。

STUFF 関数

STUFF 関数は、文字列の一部を別の文字列で置き換えるために使用できますが、連結にも利用できます。

SELECT
  STUFF((SELECT column1 FROM your_table FOR XML PATH(''), TYPE), 1, 0, column2 + ',' + column3) AS combined_column
FROM your_table;

この方法は、プラス演算子よりも柔軟性が高く、以下の利点があります。

• 連結する文字列の 数に制限はありません。
• 連結する文字列の データ型 が異なっていても** 処理できます。
• NULL 値を含む列を連結しても、結果が NULL になりません。

ただし、STUFF 関数はプラス演算子よりも 処理速度が遅い という欠点があります。

FOR XML PATH 関数

FOR XML PATH 関数は、XML 形式でデータを表現するために使用できますが、文字列の連結にも利用できます。

SELECT
  (SELECT column1, column2, column3 FOR XML PATH(''), TYPE) AS combined_column
FROM your_table;

この方法は、STUFF 関数と同様に、以下の利点があります。

また、FOR XML PATH 関数は、STUFF 関数よりも 処理速度が速い という利点もあります。

文字列の集計

GROUP BY 句

GROUP BY 句を使用して、グループごとに文字列を集計することができます。

SELECT
  group_column,
  MAX(column1) AS max_column1,
  MIN(column2) AS min_column2,
  SUM(column3) AS sum_column3
FROM your_table
GROUP BY group_column;

この方法は、グループごとに 最大値、最小値、合計値 などの集計値を取得するのに適しています。

STRING_AGG 関数

SQL Server 2016 以降では、STRING_AGG 関数を使用して、グループごとに文字列を連結することができます。

SELECT
  group_column,
  STRING_AGG(column1, ',') AS combined_column1
FROM your_table
GROUP BY group_column;

この関数は、GROUP BY 句と組み合わせて使用することで、グループごとに文字列を連結した結果を取得することができます。また、区切り文字を自由に指定できるという利点もあります。

状況ごとの最適な方法

上記の方法はそれぞれ利点と欠点があるため、状況に応じて最適な方法を選択する必要があります。

• 連結する文字列の数が少ない かつ データ型が一致している 場合は、プラス演算子 (+) を使用するのが最もシンプルです。
• 連結する文字列の数が多い かつ データ型が異なる 場合、または NULL 値を含む列 を連結する場合は、STUFF 関数または FOR XML PATH 関数を使用します。
• グループごとに文字列を集計し、最大値、最小値、合計値 などの集計値を取得する場合は、GROUP BY 句を使用します。
• グループごとに文字列を連結した結果を取得する場合は、SQL Server 2016 以降 で STRING_AGG 関数を使用します。

• 処理するデータ量が多い場合は、パフォーマンス

-- 各レコードの氏名と名前にスペースを挿入して連結する
SELECT
  FirstName + ' ' + LastName AS FullName
FROM Customers;

-- 各注文の注文明細行をカンマ区切りで連結する
SELECT
  STUFF((
    SELECT OrderDetails.ProductID, OrderDetails.Quantity
    FROM OrderDetails
    WHERE OrderDetails.OrderID = Orders.OrderID
    FOR XML PATH(''), TYPE
  ), 1, 0, '合計金額: ' + CONVERT(VARCHAR(10), SUM(OrderDetails.UnitPrice * OrderDetails.Quantity))) AS OrderDetails
FROM Orders;

-- 各部署の所属社員を XML 形式で連結する
SELECT
  DepartmentID,
  (
    SELECT EmployeeID, FirstName, LastName
    FROM Employees
    WHERE DepartmentID = Departments.DepartmentID
    FOR XML PATH(''), TYPE
  ) AS Employees
FROM Departments;

-- 各カテゴリの製品数を集計する
SELECT
  CategoryID,
  COUNT(*) AS ProductCount
FROM Products
GROUP BY CategoryID;

-- 各顧客の注文IDをカンマ区切りで連結する
SELECT
  CustomerID,
  STRING_AGG(OrderID, ',') AS OrderIDs
FROM Orders
GROUP BY CustomerID;

• 連結演算子 (||)：プラス演算子 (+) と同様に文字列を連結できますが、NULL 値を空文字 ('') に置き換えるという利点があります。

SELECT
  column1 || column2 || column3 AS combined_column
FROM your_table;

• CONVERT 関数：文字列を別のデータ型に変換してから連結することができます。データ型の変換が必要な場合に役立ちます。

SELECT
  CONVERT(VARCHAR(MAX), column1) + CONVERT(VARCHAR(MAX), column2) + CONVERT(VARCHAR(MAX), column3) AS combined_column
FROM your_table;

• SUBSTRING 関数：部分文字列を抽出して連結することができます。連結する文字列の一部のみが必要な場合に役立ちます。

SELECT
  SUBSTRING(column1, 1, LEN(column1) - 1) + SUBSTRING(column2, 2, LEN(column2) - 1) + SUBSTRING(column3, 3) AS combined_column
FROM your_table;

• CASE 式：条件に応じて異なる文字列を連結することができます。グループごとに異なる集計結果を取得する場合に役立ちます。

SELECT
  group_column,
  CASE
    WHEN COUNT(DISTINCT column1) > 1 THEN '複数の値があります'
    ELSE MAX(column1)
  END AS max_column1
FROM your_table
GROUP BY group_column;

• MIN/MAX 関数：グループごとの最小値・最大値だけでなく、対応する文字列も取得することができます。

SELECT
  group_column,
  MIN(column1) AS min_column1,
  (
    SELECT column1
    FROM your_table
    WHERE group_column = MIN(group_column)
    ORDER BY column1 LIMIT 1
  ) AS min_column1_text,
  MAX(column1) AS max_column1,
  (
    SELECT column1
    FROM your_table
    WHERE group_column = MAX(group_column)
    ORDER BY column1 DESC LIMIT 1
  ) AS max_column1_text
FROM your_table
GROUP BY group_column;

• XML PATH 式：集計結果を XML 形式で出力することができます。柔軟な結果表現が必要な場合に役立ちます。

SELECT
  group_column,
  (
    SELECT column1, column2, column3
    FOR XML PATH(''), TYPE
  ) AS aggregated_data
FROM your_table
GROUP BY group_column;

これらの方法は、それぞれ異なる利点と欠点があります。状況に合わせて最適な方法を選択してください。

• 複雑な集計や連結を行う場合は、共通表式 (CTE) や一時テーブルを使用すると効率的に処理できる場合があります。