DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: crosstab sql

  1. #1
    Join Date
    Jun 2003
    Posts
    132

    crosstab sql

    Hello,

    Is it possible to create a crosstab query? I know that my queation is not very specific, but if it is possible, can someone direct to some article, sample code, etc?

    Thanks


    Roman

  2. #2
    Join Date
    Nov 2003
    Location
    Ohio
    Posts
    51
    Search for crosstab at ask Tom . He has some examples using Oracle's decode function to do that.
    ____________________
    Pete

  3. #3
    Join Date
    Jun 2003
    Posts
    132
    Hi,

    Thanks rigatoni for the link. However, I would like to avoid using PL/SQL to get the crosstab results.

    Does anyone know how to create a crosstab like effect with SQL, where I column names would be dynamic, based on the SQL results.

    Thanks

    Roman

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    EXAMPLE CROSS TAB REPORT.


    14:29:07 SQL>DESC SALES
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    PRODUCT VARCHAR2(20)
    SALES NUMBER
    SALE_DATE DATE

    14:29:10 SQL>SELECT * FROM SALES ;

    PRODUCT SALES SALE_DATE
    -------------------- ---------- ---------
    P1 280 01-DEC-03
    P2 400 02-DEC-03
    P3 200 10-DEC-03
    P4 300 04-DEC-03
    P5 190 07-DEC-03
    P6 310 06-DEC-03
    P1 100 02-DEC-03
    P2 200 04-DEC-03
    P3 300 05-DEC-03

    9 rows selected.


    14:30:51 SQL>l
    1 SELECT PRODUCT,
    2 MAX(DECODE(RN, 1,SALE_QTY,NULL)) DEC01,
    3 MAX(DECODE(RN, 2,SALE_QTY,NULL)) DEC02,
    4 MAX(DECODE(RN, 3,SALE_QTY,NULL)) DEC03,
    5 MAX(DECODE(RN, 4,SALE_QTY,NULL)) DEC04,
    6 MAX(DECODE(RN, 5,SALE_QTY,NULL)) DEC05,
    7 MAX(DECODE(RN, 6,SALE_QTY,NULL)) DEC06,
    8 MAX(DECODE(RN, 7,SALE_QTY,NULL)) DEC07,
    9 MAX(DECODE(RN, 8,SALE_QTY,NULL)) DEC08,
    10 MAX(DECODE(RN, 9,SALE_QTY,NULL)) DEC09,
    11 MAX(DECODE(RN,10,SALE_QTY,NULL)) DEC10
    12 FROM (SELECT PRODUCT,
    13 TO_NUMBER(TO_CHAR(SALE_DATE,'DD')) RN,
    14 SUM(SALES) SALE_QTY
    15 FROM SALES
    16 GROUP BY PRODUCT, TO_NUMBER(TO_CHAR(SALE_DATE,'DD')))
    17* GROUP BY PRODUCT
    14:31:02 SQL>/

    PROD DEC01 DEC02 DEC03 DEC04 DEC05 DEC06 DEC07 DEC08 DEC09 DEC10
    ---- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
    P1 280 100
    P2 400 200
    P3 300 200
    P4 300
    P5 190
    P6 310

    6 rows selected.

    Tamil
    Last edited by tamilselvan; 02-09-2004 at 02:38 PM.

  5. #5
    Join Date
    Jun 2003
    Posts
    132
    Thanks for the idea, however, DECODE will not fit the criteria, since I would have to manually hard coded it each time. What would happen if there is a new product released? I would have to go into the code and manually update it. I am looking for a dynamic solution.

    Thank you

    Roman

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by rkiss
    Thanks for the idea, however, DECODE will not fit the criteria, since I would have to manually hard coded it each time. What would happen if there is a new product released? I would have to go into the code and manually update it. I am looking for a dynamic solution.

    Thank you

    Roman
    Dynamic sol'n = PL/SQL
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Jun 2003
    Posts
    132
    Thanks slimdave. I guess I cannot avoid the PL/SQL.

    Thanks

    Roman

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You don't need to change the SQL statement when a new product is added, however if the date period changes, then you need to modify the code.

    Tamil

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