Click here to Skip to main content
14,774,584 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
I have a table that contains the values

ItemId|ItemValue|Cost
______________________
1   A   20
1   A   38
3   B   450
4   B   28
5   C   46
6   D   87

Please find the DB Scripts for the same
CREATE TABLE Item (
  ItemId NUMBER,
  ItemValue VARCHAR2(20),
  Cost Number
);

INSERT INTO Item VALUES (1,'A',20);
INSERT INTO Item VALUES (1,'A',38);
INSERT INTO Item VALUES (3,'B',450);
INSERT INTO Item VALUES (4,'B',28);
INSERT INTO Item VALUES (5,'C',46);
INSERT INTO Item VALUES (6,'D',87);


I want the Query that Display the values like this
ItemValue  1    3     4    5     6
_____________________________
A         20
A         38
B              450
B               28
C                    28    


I will be using Oracle as my DB
Posted

Firstly Thank You for including the code snippet to create the sample data. Unfortunately I haven't been able to test this fully as sqlfiddle is down and I no longer have Oracle on this PC :(

However... you will need to PIVOT[^] the table to transpose the rows to columns - further pivot examples[^]

The first problem I see here though is that you have multiple rows in your expected results for ItemValue - usually with PIVOT you would use a function such as MIN, MAX, SUM, COUNT - If this is genuinely what you are after you might be able to achieve it with UNPIVOT (same links as above)

You could achieve it with this sql
select ItemValue, case when ItemValue = 'A' then Cost ELSE null END,
        case when ItemValue = 'B' then Cost ELSE null END,
        case when ItemValue = 'C' then Cost ELSE null END,
        case when ItemValue = 'D' then Cost ELSE null END
from Item
order by ItemValue
but this is not extendable and I very much doubt that it is what you need.
   
Comments
ravithejag 25-Feb-15 7:00am
   
Hi I tried using Pivot
select * from (
select ItemId,ItemValue,Cost from Item )
pivot(max(ItemValue) for itemid in (1,3,4,5))

But its giving the max of that itemid.

Hi CHill60,
In my scenario there are hundreds of ItemValues are present,here i can't use case statements
CHill60 25-Feb-15 8:50am
   
I thought that might be the case. I've tried playing about with joining pivot results back to the original table but that didn't work at all.
The problem is that there is nothing to distinguish [1,A,20] from [1,A,38] ... or that duplicated ID a typing error?
ravithejag 2-Mar-15 0:02am
   
I added one more column in the table, added row sequence id i.e based on itemvalue.
It solved my problem and it's working fine :)
You'll have to cheat then, by adding a unique value for every row. Like this:
SELECT  ItemValue,"1","3","4","5"
FROM    (
    SELECT  *
    FROM    (
        SELECT  ItemId
               ,ItemValue
               ,Cost
               ,Row_Number() OVER (ORDER BY itemid,itemvalue) rn
        FROM    Item
        )
pivot (
    Max(cost)
    for itemid
    IN  (1,3,4,5)
    )
    )

Here's [^]the fiddle.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900