Home » SQL & PL/SQL » SQL & PL/SQL » SQL records display
SQL records display [message #221980] Thu, 01 March 2007 06:41 Go to next message
libanori
Messages: 11
Registered: February 2007
Location: ireland
Junior Member




sql table

attribute1 value id_number

car type fiat 12
car color black 12
engine numb. 3445 12
car type opel 14
car color red 14
engine numb. 7344 14

I have a table which the records are stored in multiple fields.
For example in the above table the id_number 12 identifies one record, but it stored in three different records.

I want to display the info in this way:

car type car color engine number

fiat black 3445
opel red 7344

How can I do that?
thanks
Re: SQL records display [message #221994 is a reply to message #221980] Thu, 01 March 2007 07:30 Go to previous messageGo to next message
friendarora
Messages: 24
Registered: February 2007
Junior Member

i think we can do this in this way


select a.value,b.value,c.value
from table a,table b,table c
where a.id_number=b.id_number
and a.id_number=c.id_number
and a.attribute1='car type'
and b.attribute1='car color'
and c.attribute1='engine';


Nitin
Re: SQL records display [message #222044 is a reply to message #221994] Thu, 01 March 2007 09:10 Go to previous messageGo to next message
libanori
Messages: 11
Registered: February 2007
Location: ireland
Junior Member

Hi

Sorry I wasn't clear on the problem.

The problem is that all the values are within one table.

table name: cars:

if I do a select * from cars where attribute3=344 I get:

attribute1 attribute2 attribute3

car type fiat 344
car color black 344
engine number 198454 344

I would like display it as:

Fiat Black 344

Only in one line for each record.

thanks.



Re: SQL records display [message #222053 is a reply to message #222044] Thu, 01 March 2007 09:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
could you please use Code tags and some formatting so we can see which data belongs to which column?

I'm afraid you've been lumbered with an AWFUL database design. You have my sympathies. A quick glance in the Crystal Ball reveals that well performing queries will not feature greatly in your future development of this system.

You can try this:
SQL> create table design_test (attribute1 varchar2(30), value varchar2(30), id_number number);

Table created.

SQL> insert into design_test values ('car type'    , 'fiat' ,12); 
SQL> insert into design_test values ('car color'   , 'black',12);
SQL> insert into design_test values ('engine numb.', '3445' ,12);
SQL> insert into design_test values ('car type'    , 'opel' ,14);
SQL> insert into design_test values ('car color'   , 'red'  ,14);
SQL> insert into design_test values ('engine numb.', '7344' ,14);

SQL> select id_number
  2        ,max(decode(attribute1,'car type',    value,null)) car_type
  3        ,max(decode(attribute1,'car color',   value,null)) car_color
  4        ,max(decode(attribute1,'engine numb.',value,null)) engine_numb
  5  from   design_test
  6  group by id_number;

 ID_NUMBER CAR_TYPE                       CAR_COLOR                      ENGINE_NUMB
---------- ------------------------------ ------------------------------ -------------------------
        12 fiat                           black                          3445
        14 opel                           red                            7344


If I were you, I'd create some materialized views on these tables. They'll affect the performance of inserts and updates a bit, but your queries have a chance of running quickly.
SQL> create materialized view log on design_test
  2  with rowid, sequence(id_number,attribute1,value) including new values;

Materialized view log created.

SQL> 
SQL> create materialized view design_Test_mvw 
  2  refresh fast on commit as
  3  select id_number
  4        ,max(decode(attribute1,'car type',    value,null)) car_type
  5        ,max(decode(attribute1,'car color',   value,null)) car_color
  6        ,max(decode(attribute1,'engine numb.',value,null)) engine_numb
  7  from   design_test
  8  group by id_number;

Materialized view created.

SQL> 
SQL> select * from design_Test_mvw;

 ID_NUMBER CAR_TYPE                       CAR_COLOR                      ENGINE_NUMB
---------- ------------------------------ ------------------------------ ----------------------------
        12 fiat                           black                          3445
        14 opel                           red                            7344
Re: SQL records display [message #222242 is a reply to message #222053] Fri, 02 March 2007 07:52 Go to previous message
libanori
Messages: 11
Registered: February 2007
Location: ireland
Junior Member

Thanks guys, I got it working.
Previous Topic: Outter join in IN clause ?
Next Topic: Procedure to avoid duplicates
Goto Forum:
  


Current Time: Fri Dec 09 00:18:05 CST 2016

Total time taken to generate the page: 0.24236 seconds