PIVOT/CROSSTABLE on Oracle 9i database
Hello experts,
I have a table with id, k, v (id is is, k is key, and v is value)
like :
PIVOT not working on earlier versions of oracle database and I want a simple still reliable solution.
I really don't want to use ms access to make a crosstable for this :D
I have a table with id, k, v (id is is, k is key, and v is value)
like :
id k v <--- columns in a select * fron tempTable
------------------------
A k1 1
A k2 2
A k3 3
B k1 1
B k2 2
B k3 3
SO what I want to do is
Return the data as a crosstable/PIVOR/something else:
id k1 k2 k3
-------------------------------------
A 1 2 3
B 1 2 3PIVOT not working on earlier versions of oracle database and I want a simple still reliable solution.
I really don't want to use ms access to make a crosstable for this :D
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
awking00
Sorry, johnsone, I posted before I saw your comment.
you could also upgrade to 11 in which pivot works
ASKER
1 ) guys tried it on mysql, it worked fine with johnsone's suggestion, thanks a lot. Excellent!!
+ awking00's suggestion would probably work too, thanks!!
2) as expert please refrain from suggestion to upgrade the database, it's generally a bad suggestion unless it is asked by dba or database architects and those guys are already experts so they wouldn't need such an advice. I can't ask a globally big company to upgrade their server so I can make some data extract, it's just not good enough. But yeah if I am just experimenting things on own db/at home/garage, why not?!
+ awking00's suggestion would probably work too, thanks!!
2) as expert please refrain from suggestion to upgrade the database, it's generally a bad suggestion unless it is asked by dba or database architects and those guys are already experts so they wouldn't need such an advice. I can't ask a globally big company to upgrade their server so I can make some data extract, it's just not good enough. But yeah if I am just experimenting things on own db/at home/garage, why not?!
ASKER
Quick, sharp perception and precision in understanding the question and delivering a solution that just works, expert stuff!!
not all dba's are experts
i know a few that always use enterprise manager and don't have the first clue in writing a query
if you take away enterprise manager, they are completely useless
who hired them, i have no idea
i know a few that always use enterprise manager and don't have the first clue in writing a query
if you take away enterprise manager, they are completely useless
who hired them, i have no idea
ASKER
Geert, I am sure you have met such people mate. As far as for this problem to be solved, your suggestion to upgrade the db is a wrong one, and perhaps that is my fault not explaining fairly enough the background and stating that upgrade is not a valid choice, I'll try writing better user cases in the future.
And about the DBs, I have met some very good DBAs, who really knew their stuff, and that's always impressive, but I understand your point. I can not judge others than myself, as only myself I can improve. ;)
Thanks for your time and effort.
And about the DBs, I have met some very good DBAs, who really knew their stuff, and that's always impressive, but I understand your point. I can not judge others than myself, as only myself I can improve. ;)
Thanks for your time and effort.