[SQL Server] 縦に並んだデータを横にカンマ区切りの列データで取得する方法

縦に複数のデータを横並びで取得したい

縦にたくさん並んだデータについて、横に並べて一つの可カンマ区切りの列データとして取得したい場合があったのでその方法をまとめます。

RMDBは SQL Server です。FOR XML PATH('') を使って実現します。

例えば、部署と社員の関係を以下のようなリレーションで表現します。

部署マスタと社員マスタがあり、社員マスタには所属部署を表す部署IDを保持しています。

CreateTable

一応確認用のSQLを載せておきます.

CREATE TABLE [dbo].[部署マスタ](
    [ID] [int] NOT NULL,
    [部署名] [nvarchar](50) NOT NULL
)
CREATE TABLE [dbo].[社員マスタ](
    [ID] [int] NOT NULL,
    [部署ID] [int] NOT NULL,
    [氏名] [nvarchar](50) NOT NULL
)

INSERT [dbo].[部署マスタ] ([ID], [部署名]) VALUES (1, N'営業部')
INSERT [dbo].[部署マスタ] ([ID], [部署名]) VALUES (2, N'商品開発部')
INSERT [dbo].[部署マスタ] ([ID], [部署名]) VALUES (3, N'システム管理部')
INSERT [dbo].[社員マスタ] ([ID], [部署ID], [氏名]) VALUES (1, 1, N'山田')
INSERT [dbo].[社員マスタ] ([ID], [部署ID], [氏名]) VALUES (2, 1, N'鈴木')
INSERT [dbo].[社員マスタ] ([ID], [部署ID], [氏名]) VALUES (3, 1, N'高橋')
INSERT [dbo].[社員マスタ] ([ID], [部署ID], [氏名]) VALUES (4, 2, N'井上')
INSERT [dbo].[社員マスタ] ([ID], [部署ID], [氏名]) VALUES (5, 3, N'山下')
INSERT [dbo].[社員マスタ] ([ID], [部署ID], [氏名]) VALUES (6, 3, N'中山')

ここから社員情報をSQLで取得しようとすると、以下のようなJOINしたSQLが考えられます。

SELECT
    部署マスタ.部署名,
    社員マスタ.氏名
FROM
    部署マスタ
    INNER JOIN
        社員マスタ
    ON 部署マスタ.ID = 社員マスタ.部署ID

取得結果

このような縦に並んだデータを横に並べる、つまりこの例でいうと部署ごとに所属社員の氏名を1つのレコードに並べたいことがあります。

ここでは部署に所属する社員氏名をカンマ区切りで1つにまとめる方法を紹介します。

SQLで縦に複数のデータをカンマ区切りで取得する

結論だけ言うと、サブクエリで FOR XML PATH('') をつけてやることで実現できます。

SELECT
    部署名
    , (
        SELECT
            氏名 + ','
        FROM
            社員マスタ
        WHERE
            部署ID = 部署マスタ.ID
        FOR XML PATH('')
    ) AS 所属社員
FROM
    部署マスタ

取得結果

FOR XML PATH(”) の仕組み

FOR XML PATH('[行タグ名]') を使うと、SQLの取得結果がXML形式で、1つ(1行1列)のデータとして取得できます。

例えば社員マスタのデータをXMLで取得してみると次のようになります。

SELECT * FROM 社員マスタ
FOR XML PATH('row')

取得結果

<row>
  <ID>1</ID>
  <部署ID>1</部署ID>
  <氏名>山田</氏名>
</row>
<row>
  <ID>2</ID>
  <部署ID>1</部署ID>
  <氏名>鈴木</氏名>
</row>
<row>
  <ID>3</ID>
  <部署ID>1</部署ID>
  <氏名>高橋</氏名>
</row>
<row>
  <ID>4</ID>
  <部署ID>2</部署ID>
  <氏名>井上</氏名>
</row>
<row>
  <ID>5</ID>
  <部署ID>3</部署ID>
  <氏名>山下</氏名>
</row>
<row>
  <ID>6</ID>
  <部署ID>3</部署ID>
  <氏名>中山</氏名>
</row>

ここからわかるのは、指定した ‘row’ という要素が取得件数(行数)分生成され、中身に取得した各列のデータが列名の要素として生成されるということです。

上記例ではすべての列に名前がついているので、<ID> や <部署ID> となっていますが、名前なしの列の場合、タグの部分が生成されません。

SELECT 1
FOR XML PATH('row')

取得結果

<row>1</row>

上記SQLは 1 というデータを取得しますが、列名の指定がありませんので次のような指定した ‘row’ に囲まれただけのXMLになります。

最後に、’row’ の部分の指定をから文字にすると、タグが生成されずにデータだけが横並びに取れるということになります。データ+カンマでカンマ区切りで横に並びます。

MySQL や Oracle の場合

MySQL(MariaDB) では、GROUP_CONCAT関数を、Oracle では LISTAGG関数 を使用すると同様のことが可能なようです。

以上です。

参考URL

• FOR XML (SQL Server) | Microsoft Docs