SQL & PL/SQL

Hello,

I need a query that not only counts but also concatenates a VARCHAR2 field as well.

I have prior experience doing concatenation using ROW_NUMBER() and SYS_CONNECT_BY_PATH functions found in the below link:

http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php


The problem setup script:


CREATE TABLE CAR
(
CAR_NUM NUMBER PRIMARY KEY,
CAR_MAKE VARCHAR2(100 BYTE),
CAR_MODEL VARCHAR2(100 BYTE),
CAR_YEAR NUMBER,
CAR_DESC VARCHAR2(4000 BYTE)
);


Insert into CAR
(CAR_NUM, CAR_MAKE, CAR_MODEL, CAR_YEAR, CAR_DESC)
Values
(1, 'Toyota', 'Celica', 2002, 'Mid-sized Sports car with 28-32 MPG');
Insert into CAR
(CAR_NUM, CAR_MAKE, CAR_MODEL, CAR_YEAR, CAR_DESC)
Values
(2, 'Toyota', 'Corolla', 2006, 'Compact economy car');
Insert into CAR
(CAR_NUM, CAR_MAKE, CAR_MODEL, CAR_YEAR, CAR_DESC)
Values
(3, 'Toyota', 'Corolla', 2007, 'Compact economy car with newly redesigned features');


The results I need:

MAKE: MODEL: DESC: COUNT(YEAR):
-------------------------------------------------------------------------
Toyota | Corolla | Compact economy car;Compact economy car with newly redesigned features | 2

Toyota | Celica | Mid-sized Sports car with 28-32 MPG | 1



I can get the number of years for a specific car make/car model in the below query but can’t figure out how to get the car description to concatenate as well:

select
car_make as "Car Make",
car_model as "Car Model",
sum(1) as "Years"

from
CAR

group by
car_make, car_model


I’m open to all solutions but would prefer SQL. I’m running this on an Oracle 10g database.

Thanks in advance.