Microsoft Access 2016 SQL Unnest Array

I am trying to take a table and in Microsoft 2016 Access and use SQL to unnest a single column into multiple rows instead. It is comma separated.

SELECT BOGO_Sale.Summary, BOGO_Sale.issue_key, BOGO_Sale.promo_name, BOGO_Sale.promo_parent_sku_names, BOGO_Sale.promo_parent_skus
FROM BOGO_Sale;

I tried adding Unnest and keep getting errors, maybe I am missing something.

This BOGO_Sale.promo_parent_skus is the only column I am trying to unnest into rows.

An example of the table is:

Here is an example of what I want my SQL query to turn the data into:

The data from my source system comes this way (long story) so I just need to convert it so I can do other analysis and work on it.

The version of Microsoft Access I am using is Office 365 ProPlus Access 2016 32-bit.

Answer

unnest does not exist in MS Access SQL.

To accomplish this in MS Access, you’ll likely need to use the VBA split function to convert the content of your promo_parent_skus field into an array, and build an output table containing the results.

Something like:

Function ParseBOGO()
    Dim dbs As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim sku
    Dim fld As Field

    Set dbs = CurrentDb
    dbs.Execute "select t.* into BOGO_Output from BOGO_Sale t where false"

    Set rs1 = dbs.OpenRecordset("bogo_sale")
    Set rs2 = dbs.OpenRecordset("bogo_output")
    If Not rs1.EOF Then
        rs1.MoveFirst
        Do Until rs1.EOF
            For Each sku In Split(rs1!promo_parent_skus, ",")
                rs2.AddNew
                For Each fld In rs1.Fields
                    rs2.Fields(fld.Name) = fld.Value
                Next fld
                rs2!promo_parent_skus = sku
                rs2.Update
            Next sku
            rs1.MoveNext
        Loop
    End If
    rs2.Close
    rs1.Close
End Function

The above will generate a new table called BOGO_Output.