-
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
-
Search for crosstab at ask Tom . He has some examples using Oracle's decode function to do that.
____________________
Pete
-
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
-
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.
-
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
-
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
-
Thanks slimdave. I guess I cannot avoid the PL/SQL.
Thanks
Roman
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|