SQL & PL/SQL

Hi All,

I have a requirement to transpose the rows in single column to multiple columns. The columns are not fixed.

A table called XX_PRODUCT_SIZE.
The data in the table are,

PRODUCT_NO SIZE PRESSURE
---------------------------------------------------
P1 1 100
P1 1 200
P1 2 100
P1 2 300
P1 3 300

The data in the table are not fixed with respect to size and pressure. In future the product P1 may have size 4 with new pressure 900.

Now I want to convert these rows into multiple columns.

So it should be,

PRODUCT_NO SIZE_1 SIZE_2 SIZE_3 PRESSURE_100 PRESSURE_200 PRESSURE_300
-------------------------------------------------------------------------------------------------------------------------------------------------------
P1 TRUE TRUE TRUE
P1 TRUE TRUE TRUE
P1 TRUE TRUE


I tried many ways of writing using CASE, MAX with rownumber over partition by etc. but which will not work for me as the rows are not fixed to compare.

Can you please help me writing a query which transpose rows to columns dynamically. My intention is to create a materialized view for this XX_PRODUCT_SIZE table with transposing columns.

I am using 10g DB

Thank You
Manju

Edited by: ManjuNaik88 on May 28, 2013 2:29 AM