Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Tom Kyte

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)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

You are wrong

ryan, February 04, 2011 - 7:21 pm UTC

You are wrong.

http://stackoverflow.com/questions/213702/sql-server-2005-pivot-on-unknown-number-of-columns
Tom Kyte
February 06, 2011 - 12:04 pm UTC

Umm, how so?

first - that is sqlserver - and sqlserver isn't Oracle.

second, all of the responses there start with


I don't see any way to do it in straight SQL.

The only way I've found to do this is to use dynamic SQL and put the column labels into a variable.

I'm pretty sure this is not possible.


and so on.

Which is exactly what I said:

You must know the number of columns, their names - and then you can construct the query you need.


So, Ryan, you are wrong.

Tom you are gr8!!

Sachin, February 07, 2011 - 1:52 am UTC

Tom you are gr8!!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here