Thanks for the question, sam.
Asked: May 30, 2010 - 5:39 am UTC
Last updated: February 06, 2011 - 12:04 pm UTC
Version: 9.2.0.7.0
Viewed 1000+ times
You Asked
Hi Tom,
i finished reading all the articles about PIVOT table conversion but non of them matches my case,
i have this table
PID ATTRIBUTE_ID ATTRIBUTE_NAME ATTRIBUTE_VALUE
------- --------------- ----------------------- -------------------------------------
2054 1 COL1 Data1
2054 2 COL2 Data2
2054 3 COL3 Data3
2054 4 COL4 Data4
2066 1 COL1 Data5
2066 2 COL2 Data6
2066 3 COL3 Data7
2066 4 COL4 Data8
for reporting proposes i want it to be like this
PID COL1 COL2 COL3 COL4
------- ----------- ----------- ----------- ---------
2054 Data1 Data2 Data3 Data4
2066 Data5 Data6 Data7 Data8
the attributes are the same for all the PIDs (i.e. if there an attribute COL20 for pid 1212 then there is also an attribute COL20 for PID 332 and any other process)
the problem is that the number of attributes is unknown so i cant use the decode and the resulting table doesnt have a fixed column count
in oracle 11g its simple i can use pivot and unpivot
but i cant do that in 9i
i tried generating the sql statements in a cursor for loop and then use `EXECUTE IMMEDIATE` but the statement is some times very large in size ( around 9000 chars ) ( because the attribute count sometimes exceeds 100 ) so i cant execute the generated statement since `EXECUTE IMMEDIATE` doesnt support CLOB
i need help please
any ideas??
and Tom said...
You have to know (even in 11g) the number of columns.
9000 characters is fine for execute immediate - 32k and less is the limit in your release. You must know the number of columns, their names - and then you can construct the query you need.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment