Attempting to Transpose data in table. 2

[fiat2]

Using Oracle 9i.
I have a table that stores the records in the following fashion:

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:

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

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]

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:

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.

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]

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

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]

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]

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.