求关于两行数据合并为一行数据的sql语句



想要得到结果：



[解决办法]

WITH cars AS
(SELECT '1' carid,'东风' carname, '11' driveraid, '12' driverbid FROM dual),
 driver AS
 (SELECT '11' driverid,1 carnum,'aaa' drivername FROM dual UNION 
  SELECT '12' driverid,1 carnum,'bbb' drivername FROM dual )
SELECT CARID,
       CARNAME,
       (SELECT DRIVERNAME 
[解决办法]
 '(' 
[解决办法]
 DRIVERID 
[解决办法]
 ')'
         FROM DRIVER K
        WHERE K.DRIVERID = CARS.DRIVERAID
          AND K.CARNUM = CARS.CARID),
        (SELECT DRIVERNAME 
[解决办法]
 '(' 
[解决办法]
 DRIVERID 
[解决办法]
 ')'
         FROM DRIVER K
        WHERE K.DRIVERID = CARS.DRIVERBID
          AND K.CARNUM = CARS.CARID) FROM CARS

[解决办法]
with driver as (
select '11' as driverid,'1' as carnum,'aaa' as drivername from dual
union all
select '12' as driverid,'1' as carnum,'bbb' as drivername from dual
),
car as (
select '1' as carid,'' as carname,'11' as driveraid,'12' as driverbid from dual
)
select a.carid,
a.carname,
b.drivername as driveraname,
c.drivername as driverbname
from car a
left join driver b on a.driveraid = b.driverid
and a.carid = b.carnum
left join driver c on a.driverbid = c.driverid
and a.carid = c.carnum