SQL Server で複数の行を1行に結合する: コード例の詳細解説

SQL Serverで複数の行を1行に結合する

SQL Serverでは、複数の行を1行に結合するさまざまな方法があります。これは、データの集計や特定の形式での表示が必要な場合に特に便利です。

主な方法:

CONCAT関数

• 文字列を結合する関数です。
• 複数の列や値を連結して1つの文字列を作成します。

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

STUFF関数

• 指定した位置から既存の文字列を削除し、新しい文字列を挿入する関数です。
• 複数の行を結合し、特定の区切り文字で区切る場合に有効です。

SELECT STUFF(column1, 1, 0, (SELECT column2 FROM your_table WHERE condition)) AS combined_column
FROM your_table;

FOR XML PATH

• XML形式でデータを結合する関数です。
• 複数の行を結合し、特定のXML構造を作成する場合に便利です。

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

PIVOT関数

• 行データを列データに変換する関数です。
• 複数の行を結合し、特定の列を基準として列ヘッダーを作成する場合に有効です。

SELECT column1, [column2_value1], [column2_value2]
FROM your_table
PIVOT (MAX(column3) FOR column2 IN ([column2_value1], [column2_value2])) AS p;

UNION ALL

• 複数のクエリ結果を結合する関数です。
• 同じ構造の複数のテーブルのデータを結合する場合に便利です。

SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;

例:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID VARCHAR(50),
    ProductName VARCHAR(100),
    Quantity INT
);

INSERT INTO Orders VALUES (1, 'A001', 'Product A', 5);
INSERT INTO Orders VALUES (2, 'A001', 'Product B', 3);
INSERT INTO Orders VALUES (3, 'A002', 'Product C', 2);

-- CONCAT関数を使用して、同じ顧客の注文を1行に結合する
SELECT CustomerID, CONCAT(ProductName, ' (', Quantity, ')') AS CombinedOrder
FROM Orders
GROUP BY CustomerID;

SQL Server で複数の行を1行に結合する: コード例の詳細解説

コード例の詳細解説

先ほどご紹介したコード例を一つずつ詳しく解説していきます。

CONCAT関数を使った例

SELECT CustomerID, CONCAT(ProductName, ' (', Quantity, ')') AS CombinedOrder
FROM Orders
GROUP BY CustomerID;

• CustomerID: 顧客IDをグループ化の基準としています。
• CONCAT関数: ProductName、' ('、Quantity、')' を連結し、一つの文字列としてCombinedOrderという新しいカラムに格納します。
• GROUP BY CustomerID: 同じCustomerIDを持つ行をグループ化し、各グループに対してCONCAT関数が適用されます。

このクエリで実現すること: 同じ顧客の注文を、ProductNameとQuantityを括弧で囲んで一つの文字列として表示します。例えば、顧客A001の注文が「Product A (5), Product B (3)」のように表示されます。

STUFF関数を使った例

SELECT STUFF(column1, 1, 0, (SELECT column2 FROM your_table WHERE condition)) AS combined_column
FROM your_table;

• STUFF関数: column1の1文字目から0文字分を削除し、サブクエリで取得したcolumn2を挿入します。
• サブクエリ: 特定の条件に合致するcolumn2の値を取得します。

このクエリで実現すること: column1の値の先頭に、サブクエリで取得した値を連結します。例えば、column1が「A」、サブクエリが「B」を返した場合、「BA」という結果になります。

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

• FOR XML PATH: テーブルのデータをXML形式に変換します。
• TYPE: 結果をXMLとして扱うように指定します。

このクエリで実現すること: column1の値を全てカンマで連結した文字列を生成します。

PIVOT関数を使った例

SELECT column1, [column2_value1], [column2_value2]
FROM your_table
PIVOT (MAX(column3) FOR column2 IN ([column2_value1], [column2_value2])) AS p;

• PIVOT関数: 行データを列データに変換します。
• MAX(column3): column3の最大値を新しい列に格納します。
• FOR column2 IN ([column2_value1], [column2_value2]): column2の値を新しい列名として使用します。

このクエリで実現すること: column2の値を列ヘッダーとし、対応するcolumn3の値をセルに配置した表形式のデータを作成します。

SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;

• UNION ALL: 複数のクエリの結果を結合します。

どの方法を選ぶべきか？

• 単純な文字列連結: CONCAT関数
• 特定の位置への挿入: STUFF関数
• XML形式での結合: FOR XML PATH
• 行データを列データに変換: PIVOT関数
• 複数のテーブルの結合: UNION ALL

具体的なケースに応じて、最適な方法を選択してください。

• STRING_AGG関数: SQL Server 2017以降で利用可能。複数の行の値をカンマ区切りなどで連結する際に便利です。
• パフォーマンス: 大量のデータを扱う場合は、インデックスの作成や実行計画の確認など、パフォーマンスチューニングが重要になります。

ご自身のデータに合わせて、これらの方法を組み合わせたり、応用したりすることで、柔軟なデータ処理が可能になります。

• 上記の例はあくまで基本的なものです。実際のデータや要求に応じて、より複雑なクエリを作成することも可能です。
• SQL Serverのバージョンによって、利用可能な関数や構文が異なる場合があります。

STRING_AGG関数 (SQL Server 2017以降)

• 目的: 複数の行の値を指定された区切り文字で連結し、一つの文字列として返す。
• 特徴: CONCAT関数よりも直感的で、WITHIN GROUP句を使うことで連結順序を制御できる。

SELECT CustomerID, STRING_AGG(ProductName, ', ') WITHIN GROUP (ORDER BY ProductName) AS CombinedOrder
FROM Orders
GROUP BY CustomerID;

XML PATHの応用

• 目的: より複雑なXML構造を作成し、XQueryを用いてデータ抽出を行う。
• 特徴: 柔軟なデータ操作が可能。

SELECT 
    (
        SELECT ProductName + ',' AS [text()]
        FROM Orders
        WHERE CustomerID = o.CustomerID
        FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)') AS CombinedOrder
FROM Orders o
GROUP BY CustomerID;

Common Table Expressions (CTE)

• 目的: 中間結果を保存し、複雑なクエリを分割して可読性を高める。
• 特徴: 再帰的なクエリや階層構造のデータを扱う際に有効。

WITH ProductsPerCustomer AS (
    SELECT CustomerID, ProductName
    FROM Orders
)
SELECT CustomerID, STUFF((
    SELECT ',' + ProductName
    FROM ProductsPerCustomer p
    WHERE p.CustomerID = c.CustomerID
    FOR XML PATH(''), TYPE
), 1, 1, '') AS CombinedOrder
FROM ProductsPerCustomer c
GROUP BY CustomerID;

ユーザー定義関数 (UDF)

• 目的: 複雑なロジックをカプセル化し、再利用性を高める。
• 特徴: 独自の集計関数を作成できる。

CREATE FUNCTION dbo.CombineProducts (@CustomerID int)
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @CombinedOrder nvarchar(max);

    SELECT @CombinedOrder = STUFF((
        SELECT ',' + ProductName
        FROM Orders
        WHERE CustomerID = @CustomerID
        FOR XML PATH(''), TYPE
    ), 1, 1, '');

    RETURN @CombinedOrder;
END;

SELECT CustomerID, dbo.CombineProducts(CustomerID) AS CombinedOrder
FROM Orders
GROUP BY CustomerID;

• 単純な連結: STRING_AGG関数
• 柔軟なXML操作: FOR XML PATH
• 複雑なロジック: CTEやUDF
• パフォーマンス: データ量やクエリの複雑さによって最適な方法が異なるため、実行計画を確認する。

選択のポイント:

• 可読性: コードの理解しやすさ
• パフォーマンス: クエリの実行速度
• 柔軟性: さまざまなデータ構造に対応できるか
• 再利用性: 他のクエリで再利用できるか

• 上記以外にも、SQL Serverには様々な機能が提供されており、組み合わせることでより高度なデータ操作が可能になります。
• パフォーマンスチューニングは、大規模なデータ処理において非常に重要です。インデックスの作成や実行計画の最適化などを検討しましょう。