5 Replies Latest reply: Jun 25, 2012 8:43 AM by Frank Kulash RSS

    Help on pivoting in oracle 11g

    user12144220
      Hello,

      I trying build a dynamic report with unlimited rows and columns. So I have 3 tables. The first table has report names mean while the second and the third tables have rows and columns information respectively.
      -- table for report
      reportid reportname
      1 purchase
      2 vendor

      -- table for report rows
      rowid rowname reportid
      1 soap 1
      2 shampoo 1

      --table for report columns
      colid colname reportid
      1 liquid 1
      2 kids 1

      From those tables, I want to have a result like:
      rowname liquid 1 kids 2
      soap
      shampoo

      How do I obtain this kind of result using pivot in oracle 11g? If I can have, end users have to enter some values to the soap and liquid row, soap and kids rows,... Then I have to execute some query getting new values and column id.

      Thank you.
        • 1. Re: Help on pivoting in oracle 11g
          sb92075
          user12144220  wrote:
          Hello,

          I trying build a dynamic report with unlimited rows and columns. So I have 3 tables. The first table has report names mean while the second and the third tables have rows and columns information respectively.
          -- table for report
          reportid reportname
          1 purchase
          2 vendor

          -- table for report rows
          rowid rowname reportid
          1 soap 1
          2 shampoo 1

          --table for report columns
          colid colname reportid
          1 liquid 1
          2 kids 1

          From those tables, I want to have a result like:
          rowname liquid 1 kids 2
          soap
          shampoo

          How do I obtain this kind of result using pivot in oracle 11g? If I can have, end users have to enter some values to the soap and liquid row, soap and kids rows,... Then I have to execute some query getting new values and column id.

          Thank you.
          hows does actual report generation know, decide, or determine
          from which table the actual columns are to be obtained?

          some,many, most reports join two or more tables.
          How does this design support joining tables required for reporting against more than 1 table?

          some,many,most reports contain computed values like SUM(), COUNT(), etc.
          Are you conceding that none of your reports will contain any of these values?
          • 2. Re: Help on pivoting in oracle 11g
            887479
            http://j-oracle.blogspot.com/2012/01/listagg-and-pivot-in-oracle-11g.html
            • 3. Re: Help on pivoting in oracle 11g
              Venkadesh Raja
              try this
              WITH t AS
              (
              SELECT 1 reportid,'purchase' reportname FROM dual
              UNION ALL
              SELECT 2 reportid,'vendor' reportname FROM dual
              ),t2 AS
              (
              SELECT 1 rid,'soap' rname,1 reportid FROM dual
              UNION ALL
              SELECT 2 rid,'shampoo' rname,1 reportid FROM dual
              ),t3 AS
              (
              SELECT 1 colid,'liquid' colname,1 reportid FROM dual
              UNION ALL
              SELECT 2 colid,'kids' colname,1 reportid FROM dual
              ),t4 as
              (
              SELECT listagg(t3.colname,',')  within group(order by null) cpln,listagg(t3.colid,',')  within group(order by null) cplm
               FROM t,t2,t3 
              WHERE t.reportid=t2.rid AND t.reportid=t3.colid
              )
              select regexp_substr(cpln,'[^,]+',1,2) c,regexp_substr(cplm,'[^,]+',1,1) d,regexp_substr(cpln,'[^,]+') a,regexp_substr(cplm,'[^,]+',1,2) b
              from t4
              union
              SELECT rname,null,null,null FROM t2,t3 where t2.rid=t3.colid
              
              
              C     D     A     B
              liquid     1     kids     2
              shampoo      -      -      -
              soap      -      -      -
              • 4. Re: Help on pivoting in oracle 11g
                user12144220
                Thank all for quick help.

                Since I'm trying to build dynamic report I don't know how many columns or rows do I have. So, in my opinion, I can't union results.

                rowname          liquid          1          kids          2     
                ---------------------------------------------------------------------
                soap + + + +
                shampoo + + + +


                If have a grid like above end users can enter some values. So plus signs should be changed input values.

                Thank you.
                • 5. Re: Help on pivoting in oracle 11g
                  Frank Kulash
                  Hi,
                  user12144220  wrote:
                  ... Since I'm trying to build dynamic report I don't know how many columns or rows do I have. ...
                  Then you need dyanamic SQL.
                  Relational databases were designed to handle dynamic numbers of rows, but the number of columns in a result set is hard-coded into the query.
                  Using the Oracle 11 PIVOT feature, you can put results into an XMLTYPE column, which, when rendered by HTML, will have a dynamic numbr of columns, even though the result set only has 1 column.
                  You can also use string aggregation to produce a big VARCHAR2 column that is formatted so it looks like a dynamic number of columns.

                  See {message:id=3527823}

                  If you'd like help, post your best attempt, a little sample data (CREATE TABLE and INSERT statements), and the SQL results you want to get from that data.
                  To show the dynamic aspect of this problem, it would help if you psoted a couple of sets of data and the results from that data. For example: "If I have this data ... then the results shopuld be ... but if I add these rows to that data ... then the results should be ...".
                  Always give your exact version of Oracle. LISTAGG (available only in Oracle 11.2) might help in this problem.