none
Transpose rows in to columns in MS Access 2007. RRS feed

  • Question

  • 
    I need to transpose rows in to columns in MS Access 2007.
    
    Here we go, Below is the input data in Table01 which should be transposed.
          Table01
    +--------------+---+
    Field1   |    Field2|
    ----+--------------+
     Begdoc       ABC_001
     Enddoc       ABC_003
     DOCTYPE      EDOCS
     Begdoc       ABC_004
     Enddoc       ABC_008
     DOCTYPE      EMAIL
    +---+----+--------------+
    
    I want the result in below format :
    
               Table01
    +----------+---+---------------+
    Begdoc   |   Enddoc  |  DOCTYPE
    ----+----------------+---------+
    ABC_001      ABC_003    EDOCS
    ABC_004      ABC_008    EMAIL
    
    +---+----+--------------+
    
    How can we perform the above result in ms access ?

    Friday, May 12, 2017 2:41 PM

All replies

  • Hi,

    Yes, with some limitations, you can use a Crosstab query to transpose rows into columns.

    Give it a shot and let us know how it goes.

    Friday, May 12, 2017 3:20 PM
  • The problem with which you are faced is that there is nothing in the data to identify each subset of three rows other than their order in the table's datasheet.  I would suggest therefore that you firstly add an autonumber column, ID say, to the table.  Then examine the table carefully to be sure that the numbers sequentially follow the order of the rows in which you wish to transpose each subset of three.  Assuming this is the case you can distinctly identify each subset of three rows by integer division with (ID-1)\3.  By joining three instances of the table on this expression you can return the result table you describe with the following query:

    SELECT BegDoc, EndDoc, DOCTYPE
    FROM
       ((SELECT (ID-1)\3 AS GroupID, Field2 AS BegDoc
         FROM YourTable
         WHERE Field1 = "BegDoc") AS T1
     INNER JOIN
        (SELECT (ID-1)\3 AS GroupID, Field2 AS EndDoc
         FROM YourTable
         WHERE Field1 = "EndDoc") AS T2
      ON T1.GroupID = T2.GroupID)
     INNER JOIN
        (SELECT (ID-1)\3 AS GroupID, Field2 AS DOCTYPE
         FROM YourTable
         WHERE Field1 = "DOCTYPE") AS T3
      ON T1.GroupID = T3.GroupID
    ORDER BY T1.GroupID;

    Ken Sheridan, Stafford, England

    Friday, May 12, 2017 4:13 PM