Hi All,

Can someone help me with this issue?

We have three tables with the following structure.

SELECT * FROM market;

comp_no comp_name
100 ABC
101 DEF
102 AcME
103 XYZ
.. .
.. .
.. .
.. .
.. .

SELECT * FROM products;

pdt_no pdt_name
200 Paper
201 Staples
202 Pencil
203 Eraser
.. .
.. .
.. .
.. .
.. .
.. .


SELECT * FROM sale_data;

comp_no pdt_no sell_amt
100 200 10000
100 201 20000
100 202 30000
101 203 15000
103 200 99999
103 202 50000

I need the sale_data as a grid, with the product making the columns
and markets making the rows.

comp_no 200 201 202 203 . . . .
100 10000 20000 30000 <NULL>
101 <NULL> <NULL> <NULL> 15000
102 <NULL> <NULL> <NULL> <NULL>
103 99999 <NULL> 50000 <NULL>
.. . . . .
.. . . . .
.. . . . .
.. . . . .
.. . . . .

I know that we can do this using dynamic sql, but we have a number of
prducts in the product and it causes buffer overflow error with the
query. Highly appreciated if someone can provide any workaround or
approach to attack this problem..

Thanks

Sumith

On Mar 18, 4:27*am, sumit <sumithcn... (AT) gmail (DOT) com> wrote:
Try searching the archives on pivot table.
What version of Oracle? This looks like something that could be done
using the model statement of 10g and probably can also be done using
analytic queries 8.1.6+.

HTH -- Mark D Powell --

On Mar 18, 8:27*am, sumit <sumithcn... (AT) gmail (DOT) com> wrote:
If the columns are not known until you've read the rows,a dynamic
solution is going to be necessary.

What buffer overflow error do you get? Are you using DBMS_OUTPUT for
reporting? A dynamic ref cursor would be a better approach.