2020-02-06

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

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

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

目次

  • やりたいこと
  • テーブルの構成
  • 標準モジュールに関数の追加
  • クエリ、コード
  • 実行結果
  • 補足
  • 参考リンク

やりたいこと

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

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

テーブルの構成

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

「T_商品」テーブル

ID商品名価格
1商品A100
2商品B200
3商品C300
4商品D400
5商品E500
6商品F600
7商品G700
8商品H800

「T_注文」テーブル

ID注文番号合計金額
10000000001700
20000000002300
300000000031800

「T_注文明細」テーブル

注文ID明細NO商品ID
111
122
134
213
311
322
337
348

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

参考リンク

Access】関連記事