0 Replies Latest reply on May 2, 2012 5:34 PM by 933558

    Extracting data from rows and placing it in columns based on row data

    933558
      Oracle 10g, PL/SQL developer as the development tool

      I have 2 tables. PROGRAMDETAILDATA and PROGRAMDETAILTYPE. programdetailtype is a tables of column headings programdetaildata contains rows of data which releate to a program. They look like this, if you can visualize them.


      PROGRAMDETAILDATA
      SAVINGSKEY TYPEKEY VALUE
      5 1 2-SEP-2012
      5 2 1
      5 3 FREEZER
      5 4 PORCH
      5 5 GENERAL ELECTRIC
      5 6 UPRIGHT
      10 7 16-NOV-2011
      10 8 540202862
      10 9 SWITCH
      10 10 AC

      PROGRAMDETAILTYPE

      TYPEKEY PROGRAMKEY DISPLAYNAME
      1 3 Invoice Date
      2 3 Unit Sequence No
      3 3 Unit Type
      4 3 Unit Location
      5 3 Unit Brand
      6 3 Unit Model
      7 2 Activity Date
      8 2 Device Serial
      9 2 Device Type
      10 2 R1 APPLIANCE


      The procedure i need to create, or table, or view, or materialized view needs to do the following,
      basically read through the ProgramDetailData table and create one record for each Savingskey and place them in a table or view with the data to be in the column based on the TYPEkey. for this example, the table or view would have a max of ten (10) columns) but that needs to change if an additional program gets added and then the number of columns would increase. In addition, the names of the columns needs to be populated with the values within the PROGRAMDETAILTYPE table so the data needs to look like this:


      Savings key Invoice Date Unit Sequence No Unit Type Unit Location Unit Brand Unit Mode Activity Date Device Serial Device Type R1 APPLIANCE
      5 2-Sep-2011 1 Freezer Porch General Electric Upright
      10 16-Nov-2011 540202862 SWITCH AC


      I dont want to create a column with concatenated values. I need to create data that can be accessed through a reporting tool. Any help will be great and all questions welcome to help me get to an answer. Thanks.


      I can email a formatted version of this post to you if it would help.