Poblem with query 2

[pkohli8]

Hi,
i have table that following fields

ID Name Value Record
11 Sname Peter 1
11 Age 38 1
11 Height 62 1
11 Weight 78 1
12 Sname Sam 2
12 Age 27 2
12 Height 59 2
12 Weight 89 2
13 Sname Dave 3
13 Age 28 3
13 Height 59 3
13 Weight 99 3

now i need to create a report based on above table with following format

ID Sname Age Weight Record
11 Peter 38 78 1
12 Sam 27 89 2
13 dave 28 99 3

Thanks In advance

 

[jimbopalmer]

1) This is a horribly designed table, with lots of redundant and error prone data. If, as I suspect, this is someone's homework, they are teaching bad examples. If this is actual commercial code, look at a different vendor, soon.

2) We don't do homework.

3) Select n.id, n.value, a.value, w.value, r.value
from bad_design n, bad_design a, bad_design w,bad_design r
where n.id = a.id(+) and n.id = w.id (+) and n.id = r.id (+)
order by n.id

I leave the report heading and formatting to you

I tried to remain child-like, all I acheived was childish.

 

[SantaMufasa]

There are many methods to resolve your need, but here is one that I believe is easy to understand:

select a.id, a.sname, b.age, c.weight, a.rec
from (select id, val sname, rec from pko where name = 'Sname') a
    ,(select id, val age from pko where name = 'Age') b
    ,(select id, val weight from pko where name = 'Weight') c
 where a.id = b.id
   and a.id = c.id;

 ID SNAME      AGE        WEIGHT            REC
--- ---------- ---------- ---------- ----------
 11 Peter      38         78                  1
 12 Sam        27         89                  2
 13 Dave       28         99                  3

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

This is a horribly designed table, with lots of redundant and error prone data

Jimbo, the only redundant datum that I see is the "Record" column, which could easily become another "Name" record type.


Other than that, this method of structuring data (i.e., one data point per record) yields a highly flexible database design, that is very accommodating to changing business requirements. In fact, I have predicted for years that "data-point-per-row" data base design is the wave of the future, and as the horsepower of computers grows to meet the demands of such a data structure, development organisations are adopting this structure.

In fact, the industry standard for Genealogy-data Interchange (GEDCOMM -- GEnealogy Data COMMunication) uses precisely this format: ID, Data descriptor, Data point/value. As the application's business needs change, it does not require massive alterations to existing data structures (on the "X" axis)...it requires only a new Data descriptor and Data point/value on the "Y" axis.

So, all-in-all, the "data-point-per-row" should help reduce costs of applications maintenance and new development as more designers and developers adopt this strategy (and as hardware performance rises to meet the challenge).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[jimbopalmer]

Santa, embedding the type of row data as a varchar2 string field seems error prone and bulky compared to knowing name = 1 , age = 2 etc. and storing the numbers. (If you have to upper() the strings, all indexing is lost.)

a pointer table with
1 Name
2 Age
3 Weight
4 Record
would allow this table to be smaller, less error prone and allow faster indexing.

Your Milage May Vary.

At a lesser level of frustration, if you allow for cetain IDs NOT having some values then you need an outer join strategy. Do you make all the rows and leave some fields null or leave out some rows? All inserts need to use the same stategy.


I tried to remain child-like, all I acheived was childish.

 

[SantaMufasa]

Jimbo,

I totally agree that numeric codes are preferrable (for many reasons) to alphanumerics. I (perhaps incorrectly) inferred from your earlier comments that you were taking issue with the "data-point-per-row" concept.

In my "data-point-per-row" model, I don't complicate matters with multi-table outer joins, either...I use functions that harvest just the data points for which I am looking that satisfactorily return data values if they exist or null if they do not.

Again, this is a bit of a "futurist" design, but is becoming more of a "presentist" design as computer horsepower improves.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[jimbopalmer]

I still can't get it off my mind how much I dislike that table, using a varchar2 field to hold numeric data (or worse would have been dates) is a invatation to errors.

I tried to remain child-like, all I acheived was childish.

 

[Welshbird]

Dave - you always seem to find a really elegant solution!

I was searching for a similar answer, so have a star on me.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[maswien]

probably the same:

select t0.Id, t0.value as Sname, t1.value as Age, t2.value as weight, t0.record
from players t0, players t1, players t2
where t0.record = t1.record 
      and t1.record = t2.record
      and t0.name = 'Sname' and t1.name = 'Age' 
      and t2.name = 'Weight'