none
transform columns to rows RRS feed

  • Question

  • is there sql syntax that would transform this:

    Key         Item

    1             red

    1             yellow

    1             blue

    2             green

    2             white

    2             red

    2             black

    To this:

    Key Item   Item     Item     Item

    1   red       yellow   blue

    2   green   white     red      black

    Thursday, November 9, 2017 8:44 PM

Answers

  • Here is the basic idea:

    Sub Transform()
        Dim dbs As DAO.Database
        Dim rstKeys As DAO.Recordset
        Dim rstTarget As DAO.Recordset
        Dim rstSource As DAO.Recordset
        Dim lngIndex As Long
        Set dbs = CurrentDb
        Set rstKeys = dbs.OpenRecordset("Q1Keys", dbOpenForwardOnly)
        Set rstTarget = dbs.OpenRecordset("TargetTable", dbOpenDynaset)
        Do While Not rstKeys.EOF
            rstTarget.AddNew
            rstTarget!Id = rstKeys!Id
            lngIndex = 0
            Set rstSource = dbs.OpenRecordset("SELECT Item FROM SourceTable WHERE ID=" & _
                rstKeys!Id, dbOpenForwardOnly)
            Do While Not rstSource.EOF
                lngIndex = lngIndex + 1
                rstTarget.Fields("I" & lngIndex) = rstSource!Item
                rstSource.MoveNext
            Loop
            rstSource.Close
            rstTarget.Update
            rstKeys.MoveNext
        Loop
        rstTarget.Close
        rstKeys.Close
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, November 9, 2017 10:27 PM

All replies

  • You'd need VBA for that.

    Open a DAO (or ADODB) recordset that selects unique Key values from your source table.

    Open another recordset on the target table (which you should create before running the code).

    Loop through the first recordset.

    Within the loop, use AddNew to create a new record in the target recordset, and populate the Key field.

    Open a third recordset that selects all records from the source table with the current value of the Key field in the first recordset.

    Loop through this recordset, and use it to populate the item fields, then close this recordset.

    Update the record in the target recordset after this inner loop, and then close the third recordset.

    Close both the source and target recordsets after the outer loop.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, November 9, 2017 9:01 PM
  • sort of get what you're saying

    I make: Q1Keys        is this query distinct?  I think so

    I make: TargetTable  with these fields:

    Key   I1  I2  I3  I4  I5  I6  I7  I8

    This will limit the loop requirement to 8 which will suffice, although the source table will contain a random amount of records per key.

    ...but then I don't quite get the vba syntax for the loop with Q1Keys and SourceTable together that populates TargetTable

    Thursday, November 9, 2017 10:16 PM
  • You can use a query to number the items like this --

    Key Item CountOfItems
    1 red 1
    1 yellow 2
    1 blue 3
    2 green 1
    2 white 2
    2 red 3
    2 black 4

    Then a Crosstab query to get this --

    Key Item 1 Item 2 Item 3 Item 4
    1        red yellow blue
    2        green white red black

    NOTE - Do not use a subquery in the process!


    Build a little, test a little


    Thursday, November 9, 2017 10:22 PM
  • Here is the basic idea:

    Sub Transform()
        Dim dbs As DAO.Database
        Dim rstKeys As DAO.Recordset
        Dim rstTarget As DAO.Recordset
        Dim rstSource As DAO.Recordset
        Dim lngIndex As Long
        Set dbs = CurrentDb
        Set rstKeys = dbs.OpenRecordset("Q1Keys", dbOpenForwardOnly)
        Set rstTarget = dbs.OpenRecordset("TargetTable", dbOpenDynaset)
        Do While Not rstKeys.EOF
            rstTarget.AddNew
            rstTarget!Id = rstKeys!Id
            lngIndex = 0
            Set rstSource = dbs.OpenRecordset("SELECT Item FROM SourceTable WHERE ID=" & _
                rstKeys!Id, dbOpenForwardOnly)
            Do While Not rstSource.EOF
                lngIndex = lngIndex + 1
                rstTarget.Fields("I" & lngIndex) = rstSource!Item
                rstSource.MoveNext
            Loop
            rstSource.Close
            rstTarget.Update
            rstKeys.MoveNext
        Loop
        rstTarget.Close
        rstKeys.Close
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, November 9, 2017 10:27 PM
  • much thanks HV it works great.
    Friday, November 10, 2017 7:45 PM
  • I would be interested in how to set up that CountOfItems calculated field/column?
    Friday, November 10, 2017 7:46 PM
  • CountOfItems: DCount("*", "SourceTable", "Key=" & [Key] & " AND Item<='" & [Item] & "'")

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, November 10, 2017 8:21 PM
  • mmmmm......   Item <=   ??    that doesn't look viable.....   

    I did put it into a query and the column displays error in the field for all records.....    

    it is an interesting approach - would be interested in a way to dynamically count records by group in a query.. have only done that in a report object, never a query....may need a sequential field to do this  not sure.....

     
    Friday, November 10, 2017 10:16 PM
  • What are the data types of the Key and Item fields?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, November 11, 2017 1:46 PM
  • key is an integer, though note - definitely not sequential - so my original post is a bit misleading showing 1 then 2.

    item as posted, red, yellow, blue, etc is a string.

    Saturday, November 11, 2017 2:25 PM
  • It should work:


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, November 11, 2017 3:00 PM