How to Simulate Unpivot in Access

How to simulate UNPIVOT in Access?

This query ...

SELECT ID, A, B, C, [Key 1] AS key_field
FROM tblUnpivotSource
UNION ALL
SELECT ID, A, B, C, [Key 2] AS key_field
FROM tblUnpivotSource
UNION ALL
SELECT ID, A, B, C, [Key 3] AS key_field
FROM tblUnpivotSource;

... returns this recordset (using your sample table values as tblUnpivotSource) ...

ID A B C key_field
-- - - - ---------
 1 x y z         3
 2 x y z        57
 1 x y z       199
 2 x y z       234
 1 x y z       452
 2 x y z       452

Unpivot in Access SQL

Just use union all:

SELECT CustomerID, Phone1 as Phone
FROM dbo.CustomerPhones
UNION ALL
SELECT CustomerID, Phone2 as Phone
FROM dbo.CustomerPhones
UNION ALL
SELECT CustomerID, Phone3 as Phone
FROM dbo.CustomerPhones;

If you want to incur the overhead of removing duplicates, then use UNION.

Unpivot Data Using MS Access Query

You can use union all to unpivot in access:

select item, 'A' as period, a as forecast from tbl_weekly_forecast
union all select item, 'B', b from tbl_weekly_forecast
union all select item, 'C', c from tbl_weekly_forecast
union all select item, 'D', d from tbl_weekly_forecast

MS Access using variables to unpivot 100+ columns

Your INSERT INTO statement looks really weird, and it appears like you're not assigning the right values.

Using a querydef and parameters avoids problems with string concatenation, and minimizes some assignments.

Try the following:

Private Sub Unpivot_Click()
    Dim x As Integer
    Dim columncount As Integer
    Dim setRST As DAO.Recordset
    Dim sqlstr As String
    Dim qdf As DAO.QueryDef
    Dim fld As DAO.Field

    Set setRST = CurrentDb.OpenRecordset("Select * from TheTable")
    columncount = setRST.Fields.Count
    Set qdf = db.CreateQueryDef ("", "Insert Into TheDestination ([Template], [Row], 
    [Column], [Result]) VALUES (@Template, @RowNumber, @ColumnNumber, @Result)")
    Do While Not setRST.EOF
       qdf.Parameters("@Template") = setRST!Template
       qdf.Parameters("@RowNumber") = setRST!row
       For Each fld In setRST.Fields
           If IsNumeric(fld.Name) Then
                qdf.Parameters("@ColumnNumber") = fld.Name
                qdf.Parameters("@Result") = fld.Value
                qdf.Execute 
           End If
       Next fld
       setRST.MoveNext
    Loop

End Sub

How to unpivot a crosstab like table?

Sure

SELECT EmployeeID, "SKILL1" AS SkillID, SKILL1 AS Level_OF_Knowledge WHERE SKILL1 IS NOT NULL
UNION ALL SELECT EmployeeID, "SKILL2" AS SkillID, SKILL2 AS Level_OF_Knowledge  WHERE SKILL2 IS NOT NULL
UNION ALL SELECT EmployeeID, "SKILL3" AS SkillID, SKILL3 AS Level_OF_Knowledge  WHERE SKILL3 IS NOT NULL

*repeat last line for each additional column in your first table

unpivot data from Excel

You can use a union query.

 SELECT * INTO NewTable FROM
  (SELECT ID,someFields,Supplier,Price,Weight FROM Table
   WHERE SomeField Is Not Null
   UNION ALL
   SELECT ID,someFields1,Supplier1,Price1,Weight1 FROM Table
   WHERE SomeField1 Is Not Null
   <...>)

Transform and Pivot in Access 2016

First I tried this.

SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q01' as Question, [Q01] as Response FROM Random_data_generator UNION ALL
SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q02' as Question, [Q02] as Response FROM Random_data_generator UNION ALL
SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q03' as Question, [Q03] as Response FROM Random_data_generator UNION ALL
SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q04' as Question, [Q04] as Response FROM Random_data_generator UNION ALL
SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q05' as Question, [Q05] as Response FROM Random_data_generator UNION ALL
SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q06' as Question, [Q06] as Response FROM Random_data_generator UNION ALL
SELECT StudentID, [Questionnaire #] as QuestionnaireID, 'Q07' as Question, [Q07] as Response FROM Random_data_generator
ORDER BY StudentID, Question;

But I couldn't figure out how to do it dynamically for a random number of answers. So I blew the dust off my VB books and ended up with this which I'm sure is poorly coded and will make folks wince. But does work.

Dim db As DAO.Database
Set db = CurrentDb
Dim qdf As DAO.QueryDef

ColumnCount = CurrentDb.TableDefs("Random_data_generator").Fields.Count
ColumnCount2 = ColumnCount - 2
Dim QueryString As String
Dim QueryEntry As String
Dim counter As Integer
Dim counterEntry As String
Dim QueryTest As Recordset
Dim QuestionnaireNum As String
counter = 1


Do While counter <= ColumnCount2

 counterEntry = Format(counter, "00")

    QueryString = "SELECT StudentID, [QuestionnaireNum] as QuestionnaireID, 'Q" & counterEntry & "' as Question, [Q" & counterEntry & "] as Response FROM Random_data_generator UNION ALL " & vbCrLf
    counter = counter + 1
    QueryEntry = QueryEntry + QueryString

 Loop

 counterEntry = Format(counter, "00")

 QueryString = "SELECT StudentID, [QuestionnaireNum] as QuestionnaireID, 'Q" & counterEntry & "' as Question, [Q" & counterEntry & "] as Response FROM Random_data_generator ORDER BY StudentID, Question;"
 QueryEntry = QueryEntry + QueryString

 On Error Resume Next
 DoCmd.DeleteObject acQuery, "tempQry"
 On Error GoTo 0

 Set qdf = db.CreateQueryDef("tempQry", QueryEntry)

 DoCmd.OpenQuery ("tempQry")

Is there an easy way to un-crosstab query a table? (normalize)

Whilst Access doesn't have a single built-in function to do what you want, it is relatively easy to create something using VBA that loops the fields in the first table to output the data as required. Something like:

Sub sUnXTabData()
    On Error GoTo E_Handle
    Dim db As DAO.Database
    Dim rsSteer As DAO.Recordset
    Dim rsData As DAO.Recordset
    Dim intCount As Integer
    Dim intLoop1 As Integer
    Set db = CurrentDb
    Set rsSteer = db.OpenRecordset("SELECT * FROM tblXTab;")
    If Not (rsSteer.BOF And rsSteer.EOF) Then
        db.Execute "DELETE * FROM tblNotXTab;"
        Set rsData = db.OpenRecordset("SELECT * FROM tblNotXTab WHERE 1=2;")
        intCount = rsSteer.Fields.Count - 1
        Do
            For intLoop1 = 2 To intCount
                rsData.AddNew
                rsData("Name") = rsSteer("Name")
                rsData("Datapoint") = rsSteer("Datapoint")
                rsData("DateCat") = rsSteer.Fields(intLoop1).name
                rsData("Date") = rsSteer.Fields(intLoop1)
                rsData.Update
            Next intLoop1
            rsSteer.MoveNext
        Loop Until rsSteer.EOF
    End If
sExit:
    On Error Resume Next
    rsSteer.Close
    rsData.Close
    Set rsSteer = Nothing
    Set rsData = Nothing
    Set db = Nothing
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "sUnXTabData", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

Please note that Name and Date are reserved words in Access, so you should change them.

Regards,

---

Related Topics