5 Replies Latest reply: Feb 5, 2014 6:58 AM by Partha Sarathy S RSS

    PIVOT QUERY EXAMPLE!

    023fa390-4e3f-4b7e-aef4-b0cb7f421a19

      HI!!!!!!!!!!

      someone with a working or made a pivot query in oracle developer example

      I can provide

      please

       

        • 1. Re: PIVOT QUERY EXAMPLE!
          Jeff Smith Sqldev Pm-Oracle

          Can you speak up, we can hardly hear you.

           

          Check out the Oracle Docs for examples.

          • 4. Re: PIVOT QUERY EXAMPLE!
            AnnPricks E

            Example is given below.. Select jobwise and deptno wise maximum sal and deptno should come as a column(not a row)

            WITH qry1 AS (SELECT job,

                                 deptno,

                                 sal

                          FROM emp)

            SELECT * FROM qry1

            PIVOT(MAX(SAL) FOR (deptno) IN(10 AS "10_Maxsal",20 AS "20_Maxsal",30 AS "30_Maxsal"))

            If you want to more examples about pivot refer below link

            http://www.oracle-developer.net/display.php?id=506

            • 5. Re: PIVOT QUERY EXAMPLE!
              Partha Sarathy S

              If this is my existing data in the table.

                      ID CUSTOMER_ID PRODU   QUANTITY
              ---------- ----------- ----- ----------
                       1           1 A             10
                       2           1 B             20
                       3           1 C             30
                       4           2 A             40
                       5           2 C             50
                       6           3 A             60
                       7           3 B             70
                       8           3 C             80
                       9           3 D             90
                      10           4 A            100

              10 rows selected.

              Using PIVOT,if I want to get the sum of quantity for each customer, then see the query used for it.

              SELECT * FROM
                          (SELECT customer_id,
                                 product_code,
                                 quantity
                          FROM pivot_test)
              PIVOT (SUM(quantity) AS sum_quantity FOR (product_code) IN ('A' AS a, 'B' AS b, 'C' AS c))
              ORDER BY customer_id;

              CUSTOMER_ID A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY
              ----------- -------------- -------------- --------------
                        1             10             20             30
                        2             40                            50
                        3             60             70             80
                        4            100

               

               

              Message was edited by: Parth272025