FOR XML PATHを使ってT-SQLで1つの列に複数の値を返す

T-SQLで1つの列に複数の値を返す方法

概要:

FOR XML PATH を使用して、1つの列に複数の値をXML形式で返す方法です。

例:

SELECT
  ID,
  (
    SELECT
      value
    FROM
      (
        SELECT
          ID,
          value
        FROM
          table
        WHERE
          ID = 1
        FOR XML PATH('')
      )
    AS t
    FOR XML PATH('')
  ) AS values
FROM
  table
WHERE
  ID = 1;

出力:

<row>
  <ID>1</ID>
  <values>
    <value>value1</value>
    <value>value2</value>
  </values>
</row>

STRING_AGG

STRING_AGG 関数を使用して、1つの列に複数の値をカンマ区切りで返す方法です。

SELECT
  ID,
  STRING_AGG(value, ',') AS values
FROM
  table
WHERE
  ID = 1
GROUP BY
  ID;

ID | values
------- | --------
1   | value1,value2

STUFF

SELECT
  ID,
  STUFF((
    SELECT
      ',' + value
    FROM
      table
    WHERE
      ID = 1
    FOR XML PATH('')
  ), 1, 1, '') AS values
FROM
  table
WHERE
  ID = 1;

ID | values
------- | --------
1   | value1,value2

JSON

SELECT
  ID,
  JSON_QUERY(
    (
      SELECT
        value
      FROM
        table
      WHERE
        ID = 1
      FOR XML PATH('')
    ),
    '$'
  ) AS values
FROM
  table
WHERE
  ID = 1;

{
  "ID": 1,
  "values": ["value1", "value2"]
}

仮想テーブル

WITH t AS (
  SELECT
    ID,
    value
  FROM
    table
  WHERE
    ID = 1
)
SELECT
  ID,
  STRING_AGG(value, ',') AS values
FROM
  t
GROUP BY
  ID;

ID | values
------- | --------
1   | value1,value2

注意事項:

• パフォーマンスや可読性なども考慮する必要があります。
• 詳細については、各関数のドキュメントを参照してください。

SELECT
  ID,
  (
    SELECT
      value
    FROM
      (
        SELECT
          ID,
          value
        FROM
          table
        WHERE
          ID = 1
        FOR XML PATH('')
      )
    AS t
    FOR XML PATH('')
  ) AS values
FROM
  table
WHERE
  ID = 1;

<row>
  <ID>1</ID>
  <values>
    <value>value1</value>
    <value>value2</value>
  </values>
</row>

SELECT
  ID,
  STRING_AGG(value, ',') AS values
FROM
  table
WHERE
  ID = 1
GROUP BY
  ID;

ID | values
------- | --------
1   | value1,value2

SELECT
  ID,
  STUFF((
    SELECT
      ',' + value
    FROM
      table
    WHERE
      ID = 1
    FOR XML PATH('')
  ), 1, 1, '') AS values
FROM
  table
WHERE
  ID = 1;

ID | values
------- | --------
1   | value1,value2

SELECT
  ID,
  JSON_QUERY(
    (
      SELECT
        value
      FROM
        table
      WHERE
        ID = 1
      FOR XML PATH('')
    ),
    '$'
  ) AS values
FROM
  table
WHERE
  ID = 1;

{
  "ID": 1,
  "values": ["value1", "value2"]
}

WITH t AS (
  SELECT
    ID,
    value
  FROM
    table
  WHERE
    ID = 1
)
SELECT
  ID,
  STRING_AGG(value, ',') AS values
FROM
  t
GROUP BY
  ID;

ID | values
------- | --------
1   | value1,value2

1つの列に複数の値を返すその他の方法

XML データ型を使用して、1つの列に複数の値をXML形式で格納することができます。

SELECT
  ID,
  CAST(
    (
      SELECT
        value
      FROM
        table
      WHERE
        ID = 1
      FOR XML PATH('')
    )
    AS xml
  ) AS values
FROM
  table
WHERE
  ID = 1;

<row>
  <ID>1</ID>
  <values>
    <value>value1</value>
    <value>value2</value>
  </values>
</row>

CLR (Common Language Runtime) を使用して、C# などの言語でカスタム関数を作成し、1つの列に複数の値を返すことができます。

CREATE FUNCTION [dbo].[GetValues](@id INT)
RETURNS TABLE
AS
BEGIN
  RETURN
  (
    SELECT
      value
    FROM
      table
    WHERE
      ID = @id
  )
END;

SELECT
  ID,
  *
FROM
  [dbo].[GetValues](1);

ID | value
------- | --------
1   | value1
1   | value2

仮想テーブルを使用して、複数の列を1つの列に結合することができます。

WITH t AS (
  SELECT
    ID,
    value
  FROM
    table
  WHERE
    ID = 1
)
SELECT
  ID,
  STRING_AGG(value, ',') AS values
FROM
  t
GROUP BY
  ID;

ID | values
------- | --------
1   | value1,value2