Results 1 to 7 of 7
  1. #1
    Ianw is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Posts
    21

    UNPIVOT using a passthrough query to oracle databse

    I want to unpivot some data that resides in an oracle database table. I am using a passthrough query in access and I am trying to use the unpivot operator but I can't seem to get it to work. Here is a sample of my query.



    I keep getting the error message
    "SQL Command not properly ended.

    Can someone help?

    Thanks

    Here is the code below.


    Code:
    select
    gldata.company,tblunpvt.Amounts,tblunpvt.Period
    from
    
    (SELECT 
    COMPANY,DB_BEG_BAL as period1,DB_AMOUNT_01 as period2
    FROM LAWSON.GLAMOUNTS) gldata
    unpivot
    (Amounts for Period in (period1,period2)) as tblunpvt

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Unpivot is not a command in ms access as far as I know, you'd have to do this with a series of union queries.

    See here:

    http://stackoverflow.com/questions/7...in-access-2010

  3. #3
    Ianw is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Posts
    21
    I am using a passthrough query to try do this. Would it need to be part of the ms access commands?

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I don't know what you're asking :P


    Let's say your source data is in a query QryStuff and you want to normalize the data


    PivotRow Col1 Col2 Col3
    Test 1 5 10 15
    Test 2 6 12 24
    Test 3 7 14 28
    Test 4 8 16


    If you want to 'unpivot' this query you would have to create a union query based on this like

    Code:
    SELECT PivotRow, Col1 FROM QryStuff
    UNION ALL
    SELECT PivotRow, Col2 FROM QryStuff
    UNION ALL
    etc...

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    To define a descriptive field name for the unioned ColX fields and a field to show the source column:

    SELECT PivotRow, Col1 AS Data, 1 AS Category FROM QryStuff
    UNION ALL
    SELECT PivotRow, Col2, 2 FROM QryStuff
    UNION ALL
    etc...
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Right, if you want an alias on the column you have to define it, I didn't consider that, in my example the column label will always be COL1 because it's the first item in the union query, where june has considered what you may want the label to actually be.

  7. #7
    Ianw is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Posts
    21
    Thanks all. It doesn't look like I can use the UNPIVOT command in passthrough queries in access. Never mind. It was worth asking the question.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Passthrough queries and listboxes
    By Xipooo in forum Access
    Replies: 16
    Last Post: 03-27-2014, 02:54 PM
  2. Replies: 8
    Last Post: 02-26-2013, 06:44 PM
  3. Passthrough over a timestamp?
    By KrisDdb in forum Access
    Replies: 1
    Last Post: 01-10-2012, 06:42 PM
  4. Cannot Make Table with Passthrough Query
    By chasemhi in forum Import/Export Data
    Replies: 0
    Last Post: 12-05-2011, 01:30 PM
  5. unpivot in ms access?
    By nkpriya in forum Queries
    Replies: 2
    Last Post: 06-20-2011, 01:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums