クエリでOracleのLISTAGGのように行データを1つの文字列に変換する方法

1対Nとなっているテーブル構成のデータを元に一覧を表示するような画面を作成する場合、「N」個の方のデータをカンマ区切りの文字列にして表示したい、といったことがあります。

これを実現するために、Oracleの場合はLISTAGG()という関数が用意されていますが、Accessには残念ながら用意されてなく、調べてみると、標準モジュールに自分で用意した関数を呼び出す方法があったので、メモとして残しておきます。

今回やりたいのは、以下のサンプルの画面で示すと「注文商品」列の部分になります。

注文データ1つにつき、複数の商品データを所有しているイメージです。

テーブルは「T_商品」「T_注文」「T_注文明細」の3テーブルを用意して、それぞれのテーブルの構成やデータは以下の通りになります。

「T_商品」テーブル

ID	商品名	価格
1	商品A	100
2	商品B	200
3	商品C	300
4	商品D	400
5	商品E	500
6	商品F	600
7	商品G	700
8	商品H	800

「T_注文」テーブル

ID	注文番号	合計金額
1	0000000001	700
2	0000000002	300
3	0000000003	1800

「T_注文明細」テーブル

注文ID	明細NO	商品ID
1	1	1
1	2	2
1	3	4
2	1	3
3	1	1
3	2	2
3	3	7
3	4	8

「T_商品」と「T_注文」の関連付けを「T_注文明細」で行っています。

続いて標準モジュールに以下の関数（DJoin）を追加します。

Public Function DJoin( _
            fieldname As String _
            , tablename As String _
            , Optional wherecondition As String _
            , Optional maxlength As Integer = 255 _
            , Optional delimiter As String = "," _
            , Optional isdistinct As Boolean = False _
            , Optional orderby As String _
            ) As String
On Error GoTo ErrorHandler

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sql As String
    Dim result As String

    If maxlength <= 0 Then Exit Function

    sql = "SELECT " & IIf(isdistinct, "DISTINCT ", "") & fieldname & " " _
            & "FROM " & tablename & " " _
            & IIf(Len(wherecondition) > 0, "WHERE " & wherecondition, "") & " " _
            & IIf(Len(orderby) > 0, "ORDER BY " & orderby, "")

    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset

    rs.Open sql, cn, adOpenForwardOnly, adLockOptimistic, adCmdTableDirect
    If rs.EOF = False Then
        result = rs.GetString(adClipString, , , delimiter)
        result = Left(result, Len(result) - 1)
    End If
    rs.Close

    If Len(result) > maxlength Then
        result = Left(result _
                    , InStrRev(result, delimiter, maxlength - 2, vbBinaryCompare) - 1) _
                    & "..."
    End If
    
ExitProcedure:
On Error Resume Next
    Set rs = Nothing
    cn.Close:
    Set cn = Nothing

    DJoin = result
    Exit Function

ErrorHandler:
    result = Err & ":" & Err.Description
    Resume ExitProcedure

End Function

変数名をちょこちょこ変えていますが、基本的には参考にしたサイトにあったコードと同じです。

最初にサンプルとして示した画面（フォーム）の読み込み時に、先ほどの標準モジュールの関数を使った一覧取得用のクエリを生成し、RecordSourceに設定します。

Private Sub Form_Load()
    Dim sql As String
    sql = ""
    sql = sql & " SELECT [T_注文].* "
    sql = sql & ",DJoin(" & _
                    "  '[T_商品].[商品名]' " & _
                    ", '[T_注文明細],[T_商品]' " & _
                    ", '[T_注文明細].[商品ID] = [T_商品].[ID] AND [T_注文明細].[注文ID] = ' & [ID] " & _
                    ", 255 " & _
                    ", ',' " & _
                    ", True " & _
                    ", '[T_商品].[商品名]' " & _
                ") AS [注文商品] "
    sql = sql & " FROM [T_注文] "
    sql = sql & " ORDER BY [T_注文].[ID] "
    
    [注文検索サブ画面].Form.RecordSource = sql
End Sub

「注文検索サブ画面」は一覧表示用のサブフォームになります。

DJoin()の2番目の引数はテーブル名（tablename）を渡すようになっていますが、今回は間に関連付けテーブルがあるため、「T_注文明細」と「T_商品」テーブルをカンマ区切りで渡し、3番目の引数の抽出条件（wherecondition）で2つのテーブルを連結するようにしています。

実際に実行すると、最初に示したサンプルイメージのように、注文に紐づく商品の名称が、カンマ区切りの文字列として表示されます。

以前、DJoin関数を含んだクエリのレコードセットで、256文字以上の文字を取得しようとすると文字化けして取得できない現象があったのですが、今回、最新バージョンのAccess（2019）で試したところ、その現象は発生しませんでした。

バージョンが上がったことで発生しなくなったかどうかは不明ですが、一応、発生した時の対応を記しておくと、DJoin()関数のみクエリから除外して、取得したデータのループ処理の中で、DJoin()を呼び出すようにしました。

DJoin()関数は単純にクエリを実行して、取得したデータを連結して文字列として返しているだけなので、クエリに埋め込むだけでなく、そういった使い方も可能です。

• DJoin 関数 - リスト文字列を取得する方法 | YU-TANG's MS-Access Discovery
• DJoin 関数（ADO/GetStringメソッド使用版）