Oracle PL/SQL Denomalised Results

Question

Hi,

Given three tables: a car table, an extras table and a link table, something like:

table_car
---------
int car_id
string make
string model

table_extras
------------
int extra_id
string extra

table_car_extras_link
---------------------
int car_id
int extra_id

I'd like to write a PL/SQL stored proc that returns data in the following way:

car_id, make, model, extra[]

e.g.

1, Ford, Fiesta, sunroof;electric windows
2, BMW, M3, sports pack;alarm;sat nav
3, subary, impreza, leather seats;ABS

I'm very much a novice when it comes to databases, so any help appreciated. Note that in our real system we'll be returning 1000s of 'cars' with each car having up to about 10 'extras'

Answer 1

This should be just a view, no need for a procedure:

create view myview as
select c.car_id, c.make, c.model, WM_CONCAT(e.extra) as extras
from table_car c, table_car_extras_link l, table_extras e
where c.car_id=l.car_id and l.extra_id=e.extra_id
group by c.car_id;

WM_CONCAT is like SUM for strings.

See this page for concatenation techniques.

Answer 2

Hi ng5000,

the following will work in 9i and above, it uses Tom Kyte's concatenation function:

SELECT c.car_id, c.make, c.model, stragg(e.extra)
  FROM table_car c
  LEFT JOIN table_car_extras_link ce ON c.car_id = ce.car_id
  LEFT JOIN table_extras e ON ce.extra_id = e.extra_id
GROUP BY c.car_id, c.make, c.model