Re: Rows into Columns, Datagrid preparation without Dynamic SQL

From: William Robertson <williamr2019_at_googlemail.com>
Date: Thu, 19 Mar 2009 00:41:58 -0700 (PDT)
Message-ID: <871121ab-6b19-43a3-a04b-708fc5680a2d_at_o11g2000yql.googlegroups.com>



On Mar 18, 8:27 am, sumit <sumithcn..._at_gmail.com> wrote:
> 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

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. Received on Thu Mar 19 2009 - 02:41:58 CDT

Original text of this message