How can I show rows as columns efficiently ?

[fgonzalezf]

Hi, I have the sales of each client for a week and I need to show
the information week by week, like this:

Note: I don´t need to show the periods (.) I wrote them in the
email only to show columns in the example.

SAL_CLI_ID SAL_PRO_CO week 1 week 2 week 3
---------- ---------- ---------- ---------- ----------
........ 1 A .............. 70
........ 1 B .............. 50
........ 1 C .....................................2
........ 2 A ......................... 91
........ 3 A .............. 11
........ 3 C ......................... 44

How can I do this query dinamically and efficiently, when I
don´t know the weeks that the user want to see.

The table is:

SQL> desc sales
Name Type
----------------------------- -----------------
SAL_CLI_ID NUMBER(10)
SAL_PRO_COD VARCHAR2(10)
SAL_YEAR NUMBER(4)
SAL_WEEK NUMBER(2)
SAL_QUANTITY NUMBER(10)

and the data in the table is:


SAL_CLI_ID SAL_PRO_CO SAL_YEAR SAL_WEEK SAL_QUANTITY
---------- ---------- ---------- ---------- ------------
....... 1 ........ A ......... 2002......... 1 ......... 70
....... 1 ........ B ......... 2002......... 1 ......... 50
....... 1 ........ C ......... 2002 ........ 3 ......... 2
....... 2 ........ A ......... 2002 ........ 2 ......... 91
....... 3 ........ A ......... 2002 ........ 1 ......... 11
....... 3 ........ C ......... 2002 ........ 2 ......... 44

I did the query below, but I would like to know another form to do this more efficiently when I don´t know the weeks that the user want to see or when I need to show a lot of weeks.

select sal_cli_id, sal_pro_cod,
decode(sal_week,1,sal_quantity,null) "week 1",
decode(sal_week,2,sal_quantity,null) "week 2",
decode(sal_week,3,sal_quantity,null) "week 3"
from sales
order by sal_cli_id, sal_pro_cod;

Thanks,

Fernando.

[chrisrlong]

Actually, if you're going to the trouble of making a different column for each week, wouldn't the next logical step be to compress the rows, such that A appears on one row instead of 3?? Of course, to do that, you would lose the SAL_CLI_ID. Expanding the columns without compressing the rows is rather odd, but I'll get over it

Using DECODE (or CASE, which is slightly faster) is the only real solution to the problem. Of course, you could always create an 'identity matrix' table with the various weeks in it, but it doesn't necessarily buy you much, especially when a variable number of weeks are involved.

Basically, use just what you have except *dynamically* build the SQL string itself. Determine how many weeks the user wants and then build the SELECT with enough DECODEs to handle it. Building a string is very fast, and the DECODEs will add virtually no overhead. All that matters is how efficiently you SELECT the actual data from the table (the WHERE clause).

Enjoy,

- Chris

[pras]

Hi
this url may help u....

http://asktom.oracle.com/pls/ask/f?p...20%7Btables%7D

thanks
pras

[pras]

Hi
this url may help u....

http://asktom.oracle.com/pls/ask/f?p...20%7Btables%7D

thanks
pras