SQL 多笔数据合并一笔

请教，现需要将多列数据合并到一行，多列数据需要从多个表中，选取数据。

SELECT*FROM SFISM4.R_WIP_TRACKING_T
WHERE SERIAL_NUMBER = '4M04230C401100'

SELECT*FROM SFISM4.R_WIP_KEYPARTS_T
WHERE SERIAL_NUMBER = '4M04230C401100'

需要夺表查询
select B.SERIAL_NUMBER,
b.KEY_PART_SN,a.MODEL_NAME,a.PALLET_NO,
a.CONTAINER_NO,a.IN_STATION_TIME
from sfism4.r_wip_tracking_t a,SFISM4.R_WIP_KEYPARTS_T b
where a.SERIAL_NUMBER=b.SERIAL_NUMBER and a.SERIAL_NUMBER IN ('4M04230C401100')

SERIAL_NUMBER KEY_PART_SN MODEL_NAME PALLET_NO CONTAINER_NO IN_STATION_TIME
4M04230C401100 CMS0J03A43D1 8191MED1B020X 000000000 N/A 11/18/2016 09:32:00
4M04230C401100 YUS50AB00BH& 8191MED1B020X 000000000 N/A 11/18/2016 09:32:00

需求为
SERIAL_NUMBER KEY_PART_SN KEY_PART_SN_1 MODEL_NAME PALLET_NO CONTAINER_NO IN_STATION_TIME
4M04230C401100 CMS0J03A43D1 YUS50AB00BH& 8191MED1B020X 0000000 N/A 11 / 18/2016 09:32:00
谢谢各位好事

SELECT
a.SERIAL_NUMBER,
b.KEY_PART_SN AS KEY_PART_SN_1,
c.KEY_PART_SN AS KEY_PART_SN_2,
a.MODEL_NAME,
a.PALLET_NO,
a.CONTAINER_NO,
a.IN_STATION_TIME
FROM
SFISM4.R_WIP_TRACKING_T a
LEFT JOIN (
SELECT KEY_PART_SN, SERIAL_NUMBER
FROM SFISM4.R_WIP_KEYPARTS_T
WHERE SERIAL_NUMBER = '4M04230C401100' AND ROWNUM = 1
) b ON a.SERIAL_NUMBER = b.SERIAL_NUMBER
LEFT JOIN (
SELECT KEY_PART_SN, SERIAL_NUMBER
FROM SFISM4.R_WIP_KEYPARTS_T
WHERE SERIAL_NUMBER = '4M04230C401100' AND ROWNUM = 2
) c ON a.SERIAL_NUMBER = c.SERIAL_NUMBER
WHERE a.SERIAL_NUMBER = '4M04230C401100';