I have the following simple example:
CREATE TABLE Cars ( Cars, Item, Value ) AS
SELECT 'bmw', 'wheels', '4' FROM DUAL UNION ALL
SELECT 'bmw', 'color', 'red' FROM DUAL UNION ALL
SELECT 'bmw', 'price', '5' FROM DUAL UNION ALL
SELECT 'mercedes', 'wheels', '4' FROM DUAL UNION ALL
SELECT 'mercedes', 'color', 'black' FROM DUAL UNION ALL
SELECT 'lambo', 'wheels', '5' FROM DUAL UNION ALL
SELECT 'lambo', 'color', 'yellow' FROM DUAL UNION ALL
SELECT 'lambo', 'price', '7' FROM DUAL UNION ALL
SELECT 'mercedes', 'price', '6' FROM DUAL;
The thing is that I need to "pivot" the table to get items as column names with values as values and everything grouped together (row with unique car name and no null values in cells). Hence I wrote "pivot", because classic pivoting (DECODE) leads to different result - decreasing skew values in cells and a lot of null values. So it would looks like this
car wheels color price
-------- ------ ------ -----
bmw 4 red 5
lambo 5 yellow 7
mercedes 4 black 6
So the question:
Should I do this via procedures or is there any more elegant solution? Procedure would be following (in pseudocode):
1. create table cars2 /*columns are known in before, wheels/color/price*/
2. get the distinct names of the cars and insert them into collection /*eg nested table*/
3. for each car do
insert into table cars2
values per item /*looping items and inserting corresponding values*/
OK, code seems simple. But having more than half a milion of records and 15 items, and updating the table once a hour may lead to real performance problems.
Try the query below;-
select * from
(select cars, item,value from carTable)
pivot(max(value) for item in ('wheels', 'color', 'price'))