Re: Rows into Columns, Datagrid preparation without Dynamic SQL

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Wed, 18 Mar 2009 06:53:18 -0700 (PDT)
Message-ID: <51ae9872-3432-4ed2-a6d2-c7a02c314917_at_w35g2000yqm.googlegroups.com>



On Mar 18, 4: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

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 -- Received on Wed Mar 18 2009 - 08:53:18 CDT

Original text of this message