Transpose rows to columns in Oracle 11g

Question

0.00/5 (No votes)

See more:

Hi,
I have a table that contains the values

HTML

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

SQL

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

HTML

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 24-Feb-15 23:13pm

ravithejag

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2015-02-24T23:42:00

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

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.

Jörgen Andersson · Answer 2 · 2015-03-01T10:12:00

You'll have to cheat then, by adding a unique value for every row. Like this:

SQL

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.

Transpose rows to columns in Oracle 11g

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0