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

PIVOT QUERY EXAMPLE!

023fa390-4e3f-4b7e-aef4-b0cb7f421a19 Newbie
Currently Being Moderated

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 ACE Moderator
    Currently Being Moderated

    Can you speak up, we can hardly hear you.

     

    Check out the Oracle Docs for examples.

  • 4. Re: PIVOT QUERY EXAMPLE!
    AnnPricks E Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points