INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

...I enjoy spending time on it for personal growth (I learn from the questions I don't answer, and I reinforce/stay sharp on the topics I do answer), and to give back to humanity at large...

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > DBMS Packages > Oracle: Oracle release - 9i and earlier Forum

Attempting to Transpose data in table.(2)

CODE

prodID | sample | attribute | value
-----------------------------------
1445a  | 1      | Thickness |  13.9
1445a  | 2      | Thickness |  14
1445a  | 3      | Thickness |  13.8
1445a  | 1      | Drop      |  0
1445a  | 2      | Drop      |  .1
1445a  | 3      | Drop      |  .1
1445a  | 1      | Height    |  .1
1445a  | 2      | Height    |  1
1445a  | 3      | Height    |  1

I wish to return the data like this:

CODE

prodID | sample | Thickness  | Drop   | Height
-------------------------------------------------
1445a  |  1     |  13.9      |  0     | .1
1445a  |  2     |  14        | .1     | 1
1445a  |  3     |  13.8      | .1     | 1

Here is my current SQL

CODE

SELECT ad.prodID, ad.sample, ad1.value as Thickness, ad2.value as Drop, ad3.value as Height
FROM mv_audit_data ad,
mv_audit_data ad1,
mv_audit_data ad2,
mv_audit_data ad3
where ad.prodID = <VALUE>
and ad.sample = ad1.sample
and ad.sample = ad2.sample
and ad.sample = ad3.sample
and ad.prodID = ad1.prodID
and ad.prodID = ad2.prodID
and ad.prodID = ad3.prodID
and ad1.attribute = 'Thickness'
and ad2.attribute = 'Drop'
and ad3.attribute = 'Height'
group by ad.prodID, ad.sample, ad1.value, ad2.value, ad3.value

The problem is that not all prod IDs will contain values for all attributes (Thickness, Drop, Height, etc...). If that is the case, no records are returned for the ProdID.

Any ideas? What is the best approach for this?

thanks for your time!

SantaMufasa (TechnicalUser)

30 Sep 09 17:28

Fiat,

You can accomplish what you want with the use of Oracle's outer join functionality. In the sample, below, the outer-join operator that I use, (+), will work on any version of Oracle. (There is other outer-join syntax available in more recent versions of Oracle, but I'll not illustrate the alternate syntax here.)

I've added one row of data to simulate your case of a sample with missing attributes. I've also retained your original code for the most part, added the outer-join operator, and used an in-line view for your driver table ("ad") just because it seemed more efficient:

CODE

SQL> select * from mv_audit_data;

PRODI     SAMPLE ATTRIBUTE       VALUE
----- ---------- ---------- ----------
1445a          1 Thickness        13.9
1445a          2 Thickness          14
1445a          3 Thickness        13.8
1445a          1 Drop                0
1445a          2 Drop               .1
1445a          3 Drop               .1
1445a          1 Height             .1
1445a          2 Height              1
1445a          3 Height              1
1445a          4 Drop              .15

10 rows selected.

SELECT ad.prodID, ad.sample, ad1.value as Thickness, ad2.value as Drop_val, ad3.value as Height
FROM (select distinct prodid, sample from mv_audit_data where prodid = '1445a') ad,
mv_audit_data ad1,
mv_audit_data ad2,
mv_audit_data ad3
where ad.sample = ad1.sample(+)
and ad.sample = ad2.sample(+)
and ad.sample = ad3.sample(+)
and ad.prodID   = ad1.prodID(+)
and ad.prodID = ad2.prodID(+)
and ad.prodID = ad3.prodID(+)
and ad1.attribute(+) = 'Thickness'
and ad2.attribute(+) = 'Drop'
and ad3.attribute(+) = 'Height'
group by ad.prodID, ad.sample, ad1.value, ad2.value, ad3.value
order by prodid, sample
/

PRODI     SAMPLE  THICKNESS   DROP_VAL     HEIGHT
----- ---------- ---------- ---------- ----------
1445a          1       13.9          0         .1
1445a          2         14         .1          1
1445a          3       13.8         .1          1
1445a          4                   .15

4 rows selected.

Rather than my answering questions here that you might not have, I'll wait for you to ask questions about this technique if you actually have questions.

santa Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
"Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty."

Dagon (MIS)

1 Oct 09 3:53

A better technique that joining the table together multiple times is to use a CASE..GROUP BY method.

CODE

select prodid,
       sample,
       sum(case when attribute = 'Thickness' then value end) as thickness,
       sum(case when attribute = 'Drop' then value end) as dropval,
       sum(case when attribute = 'Height' then value end) as height
from mv_audit_data
group by prodid,
         sample
order by prodid,
         sample

fiat2 (Programmer)

1 Oct 09 12:10

Great! Thanks for the information.
After a quick query, it does appear that the Case statement is returning the data more efficiently.

Santa, Thanks for introducing me to In-Line Views (hanging my head in shame for not knowing...)

Again, thanks for the quick replies.

Enjoy.

fiat2 (Programmer)

1 Oct 09 12:11

Great! Thanks for the information. Both appear to solve my requirement.

After a quick query, it does appear that the Case statement is returning the data more efficiently.

Santa, Thanks for introducing me to In-Line Views (hanging my head in shame for not knowing...)

Again, thanks for the quick replies.

Enjoy.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Oracle: Oracle release - 9i and earlier

Member Login

Come Join Us!

LINK TO THIS FORUM!

Tek-Tips LinkedIn® Group

Partner With Us!

Feedback

Geography

Tek-Tips Shirts!

Attempting to Transpose data in table.(2)

CODE

CODE

CODE

CODE

CODE

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Back To Forum

Join Tek-Tips® Today!

Tek-Tips LinkedIn^® Group

Join Tek-Tips^® Today!