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
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