Group By Query Help

I have two tables ‘VEHICLE’ and ‘VEHICLE_CLASS’ as per below:
VEHICLE_CLASS
-------------------------------------------
VEHICLE_ID | VEHICLE_CLASS_ID | STATUS
-------------------------------------------
vehicle_001 | vehicle_class_001 | 0
vehicle_002 | vehicle_class_002 | 1
vehicle_003 | vehicle_class_003 | 2
vehicle_004 | vehicle_class_001 | 0
vehicle_005 | vehicle_class_002 | 2
vehicle_006 | vehicle_class_001 | 0
vehicle_007 | NULL | 1
----------------------------------------------


VEHICLE
------------------------------------------
VEHICLE_CLASS_ID | VEHICLE_CLASS_NAME
-----------------------------------------
vehicle_class_001 | ABC
vehicle_class_002 | BCD
vehicle_class_003 | EFG
vehicle_class_004 | XYZ
vehicle_class_005 | PQR
vehicle_class_006 | STU
---------------------------------------

*There are three statuses 0=Initiated, 1=In Progress, 2=Completed


I need a query to get the result rows as per below:
-----------------------------------------------------------------------
VEHICLE_CLASS_NAME | COMPLETED_ VEHICLES | NOT_COMPLETED_ VEHICLES
---------------------------------------------------------------------------
ABC | |vehicle_001, vehicle_004, vehicle_006
BCD | vehicle_005 |vehicle_002
EFG | vehicle_003 |

* If Status=2 then completed else it is not completed.


Please see attached image to view tables.
Please help me, how can I get result rows.
Thank you.

Do you actually expect us to do your homework?

If you want to learn, you should at least try to code the query on your own -- that is the purpose of homework!
:rolleyes:

I am sorry, you take it that way.

This is not the homework.

I changed the tables so that I can't share my company's data.

Also Here is what I have tried in MySQL,
SELECT veh.VEHICLE_CLASS_ID, vehclass.VEHICLE_CLASS_NAME,
IF(STATUS=2,GROUP_CONCAT(`VEHICLE_ID`),'') COMPLETED_ VEHICLES,
IF(STATUS<2,GROUP_CONCAT(`VEHICLE_ID`),'') NOT_COMPLETED_ VEHICLES
FROM VEHICLE veh LEFT JOIN VEHICLE_CLASS vehclass
on veh.VEHICLE_CLASS_ID = vehclass.VEHICLE_CLASS_ID GROUP BY veh.VEHICLE_CLASS_ID having veh.VEHICLE_CLASS_ID is not null;


But its not working.

I need the solution in four different databases, i.e. Oracle, MySQL, MSSQL and IBM DB2.

I know it has to do something with 'DECODE' function in Oracle but as a newbie, I am not proficient in writing such complex queries.

You can give me more hint that will be OK. I am not asking for complete query.

Here is the MySQL query solution for above post:

SELECT vc.vehicle_class_name
, GROUP_CONCAT(CASE WHEN v.status = 2 THEN v.vehicle_id END) completed
, GROUP_CONCAT(CASE WHEN v.status <> 2 THEN v.vehicle_id END) incomplete
FROM vehicle_class vc
JOIN vehicle v
ON v.vehicle_class_id = vc.vehicle_class_id
GROUP
BY vc.vehicle_class_id;

I need help converting in Oracle9i, DB2 and MSSql.

Please help.

Quote:

---

Originally Posted by deduyay

I have two tables ‘VEHICLE’ and ‘VEHICLE_CLASS’ as per below:
VEHICLE_CLASS
-------------------------------------------
VEHICLE_ID | VEHICLE_CLASS_ID | STATUS
-------------------------------------------
vehicle_001 | vehicle_class_001 | 0
vehicle_002 | vehicle_class_002 | 1
vehicle_003 | vehicle_class_003 | 2
vehicle_004 | vehicle_class_001 | 0
vehicle_005 | vehicle_class_002 | 2
vehicle_006 | vehicle_class_001 | 0
vehicle_007 | NULL | 1
----------------------------------------------


VEHICLE
------------------------------------------
VEHICLE_CLASS_ID | VEHICLE_CLASS_NAME
-----------------------------------------
vehicle_class_001 | ABC
vehicle_class_002 | BCD
vehicle_class_003 | EFG
vehicle_class_004 | XYZ
vehicle_class_005 | PQR
vehicle_class_006 | STU
---------------------------------------

*There are three statuses 0=Initiated, 1=In Progress, 2=Completed


I need a query to get the result rows as per below:
-----------------------------------------------------------------------
VEHICLE_CLASS_NAME | COMPLETED_ VEHICLES | NOT_COMPLETED_ VEHICLES
---------------------------------------------------------------------------
ABC | |vehicle_001, vehicle_004, vehicle_006
BCD | vehicle_005 |vehicle_002
EFG | vehicle_003 |

* If Status=2 then completed else it is not completed.


Please see attached image to view tables.
Please help me, how can I get result rows.
Thank you.

---

Two notes.

1- Result set is not clear; only vehicle_001 is of class ABC but three vehicles are listed in the report ?!

2- Do not think DECODE() will cut it for you, specially if you need to write a query that is compatible with four different RDBMS engines.