CROSS APPLYで親子関係のあるデータを効率的に変換

SQL Serverで効率的に行を列に変換する方法

方法

1. PIVOT クエリ：
   • 最も一般的な方法
   • 列名と値のペアを生成
   • 複数の列を軸としてピボットできる
   • 集計関数と組み合わせて使用できる

例：

SELECT
    [Name],
    [Age],
    [Gender]
FROM
    [dbo].[Customers]
PIVOT
(
    MAX([Salary])
    FOR [Gender] IN ([Male], [Female])
) AS PivotTable;

1. FOR XML PATH：
   • XML 形式で出力
   • 複雑な変換に適している
   • XSLT を使ってさらに処理できる

SELECT
    [Name],
    (
        SELECT
            [Age]
        FOR XML PATH(''),
        TYPE
    ) AS [Ages]
FROM
    [dbo].[Customers];

1. CASE 式：
   • 複数の条件に基づいて値を変換できる

SELECT
    [Name],
    CASE
        WHEN [Gender] = 'Male' THEN '男性'
        WHEN [Gender] = 'Female' THEN '女性'
        ELSE '不明'
    END AS [GenderText]
FROM
    [dbo].[Customers];

1. T-SQL スクリプト：
   • 柔軟性が高い

DECLARE @table TABLE
(
    [Name] VARCHAR(50),
    [Value] INT
);

INSERT INTO @table ([Name], [Value])
VALUES ('A', 1), ('B', 2), ('C', 3);

DECLARE @xml XML = (
    SELECT
        [Name],
        (
            SELECT
                [Value]
            FOR XML PATH(''),
            TYPE
        ) AS [Values]
    FROM
        @table
);

SELECT
    [Name],
    [Values].value('.', 'varchar(50)') AS [Value]
FROM
    @xml.nodes('/row');

効率化のヒント

• 適切なインデックスを作成する
• 不要な列をSELECTしない
• CTE (Common Table Expressions) を使用する
• ビューを作成する

上記の情報は参考用であり、最新の情報ではない可能性があります。詳細については、SQL Serverの公式ドキュメントを参照してください。

SELECT
    [Name],
    [Age],
    [Gender]
FROM
    [dbo].[Customers]
PIVOT
(
    MAX([Salary])
    FOR [Gender] IN ([Male], [Female])
) AS PivotTable;

このクエリは、Customers テーブルから Name、Age、Gender、Salary 列を取得し、Gender を軸としてピボットします。結果は以下のようになります。

Name | Male | Female
------- | -------- | --------
John | 100000 | NULL
Jane | NULL | 80000

FOR XML PATH

SELECT
    [Name],
    (
        SELECT
            [Age]
        FOR XML PATH(''),
        TYPE
    ) AS [Ages]
FROM
    [dbo].[Customers];

Name | Ages
------- | --------
John | <Age>20</Age>
Jane | <Age>30</Age>

CASE 式

SELECT
    [Name],
    CASE
        WHEN [Gender] = 'Male' THEN '男性'
        WHEN [Gender] = 'Female' THEN '女性'
        ELSE '不明'
    END AS [GenderText]
FROM
    [dbo].[Customers];

Name | GenderText
------- | --------
John | 男性
Jane | 女性

T-SQL スクリプト

DECLARE @table TABLE
(
    [Name] VARCHAR(50),
    [Value] INT
);

INSERT INTO @table ([Name], [Value])
VALUES ('A', 1), ('B', 2), ('C', 3);

DECLARE @xml XML = (
    SELECT
        [Name],
        (
            SELECT
                [Value]
            FOR XML PATH(''),
            TYPE
        ) AS [Values]
    FROM
        @table
);

SELECT
    [Name],
    [Values].value('.', 'varchar(50)') AS [Value]
FROM
    @xml.nodes('/row');

このスクリプトは、@table テーブルを作成し、Name と Value 列を挿入します。次に、FOR XML PATH を使って Value 列を XML 形式に変換します。最後に、`X

SQL Serverで効率的に行を列に変換する方法 - その他の方法

1. CROSS APPLY：
   • 1 つの行に対して複数の行を生成
   • 親子関係のあるデータの変換に適している

SELECT
    [Customer].Name,
    [Order].OrderID,
    [Order].OrderDate
FROM
    [dbo].[Customers]
CROSS APPLY
(
    SELECT
        *
    FROM
        [dbo].[Orders]
    WHERE
        [CustomerID] = [Customer].ID
) AS [Order];

1. JOIN：
   • 複数のテーブルからデータを取得

SELECT
    [Customer].Name,
    [Order].OrderID,
    [Order].OrderDate
FROM
    [dbo].[Customers]
INNER JOIN
    [dbo].[Orders] ON [Customer].ID = [Order].CustomerID;

1. SUBQUERY：

SELECT
    [Name],
    (
        SELECT
            MAX([Salary])
        FROM
            [dbo].[Employees]
        WHERE
            [DepartmentID] = [Employee].DepartmentID
    ) AS [MaxSalary]
FROM
    [dbo].[Employees];