data required in pyramid format
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: data required in pyramid format

  1. #1
    Join Date
    Mar 2008
    Posts
    12

    data required in pyramid format

    Hi,
    I have a table having below data.Each row_id is having a date associated with it.

    table structure:

    create table test_pyramid
    (dated date,
    count number,
    row_id number
    )


    DATED COUNT ROW_ID
    07/02/2008 129 1
    07/03/2008 130 1
    07/04/2008 131 1
    07/05/2008 132 1
    07/06/2008 133 1
    07/03/2008 1090 2
    07/04/2008 1091 2
    07/05/2008 1092 2
    07/06/2008 1093 2
    07/04/2008 591 3
    07/05/2008 592 3
    07/06/2008 593 3

    I want to display the data in the below pyramid format.


    DATED COUNT COUNT_1 COUNT_2
    07/02/2008 129
    07/03/2008 130 1090
    07/04/2008 131 1091 591
    07/05/2008 132 1092 592
    07/06/2008 133 1093 593
    I used below query to get the format but the problem is row_id can increase upto 60 and so will the dated entry.
    I need a dynamic query to create such pyramid.

    select a.dated,a.count,b.count,c.count FROM TEST_PYRAMID a,
    (select dated ,count FROM TEST_PYRAMID where row_id=2) b,
    (select dated ,count FROM TEST_PYRAMID where row_id=3) c
    where a.dated=b.dated(+)
    and b.dated=c.dated(+)
    and a.row_id=1
    order by 1

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    why not resort to the old-and-good pivot table solution?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Mar 2008
    Posts
    12
    Hi,

    The row_id is not actuall fixed to 3 .It can increase upto 60.i.e columns can increase upto 60.
    So how to write dynamic pivot query?
    thanks

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    read again, slowly.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width