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.

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

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

Quote:

---

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