Rows into Columns, Datagrid preparation without Dynamic SQL

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 Received on Wed Mar 18 2009 - 03:27:07 CDT