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