我有两张table如下：
苹果桌：

+----------+----------+---------+
| APPLE_ID | PHONE_ID | IPAD_ID |
+----------+----------+---------+
|        1 |     1001 |    2001 |
|        2 |     1002 |    2002 |
|        3 |     1003 |    2003 |
|        4 |     1004 |    2004 |
+----------+----------+---------+

表keey：

+---------+----------+--------+-----------+
| KEEY_ID |   NAME   | DTL_ID | DEVICE_ID |
+---------+----------+--------+-----------+
|       1 | PHONE_ID |   1001 |       111 |
|       2 | PHONE_ID |   2001 |       111 |
|       3 | IPAD_ID  |   2001 |       222 |
|       4 | PHONE_ID |   1003 |       444 |
|       5 | MAC_ID   |    367 |       333 |
+---------+----------+--------+-----------+

期望输出：

+----------+----------+---------+-----------------+----------------+
| APPLE_ID | PHONE_ID | IPAD_ID | PHONE_DEVICE_ID | IPAD_DEVICE_ID |
+----------+----------+---------+-----------------+----------------+
|        1 |     1001 |    2001 |             111 | 222            |
|        3 |     1003 |    2003 |             444 | null           |
+----------+----------+---------+-----------------+----------------+

迄今为止尝试的代码：

SELECT 
APPLE.APPLE_ID,
APPLE.PHONE_ID,
APPLE.IPAD_ID,
NULL AS IPHONE_DEVICE_ID,
KY.DEVICE_ID AS IPAD_DEVICE_ID
FROM APPLE
LEFT JOIN KEEY KY ON APPLE.IPAD_ID=KY.DTL_ID WHERE KY.NAME='IPAD_ID'
UNION
SELECT 
APPLE.APPLE_ID,
APPLE.PHONE_ID,
APPLE.IPAD_ID,
KY.DEVICE_ID AS PHONE_DEVICE_ID,
NULL AS IPAD_DEVICE_ID
FROM APPLE
LEFT JOIN KEEY KY ON APPLE.PHONE_ID=KY.DTL_ID WHERE KY.NAME='PHONE_ID'

这给了我：

+----------+----------+---------+------------------+----------------+
| APPLE_ID | PHONE_ID | IPAD_ID | IPHONE_DEVICE_ID | IPAD_DEVICE_ID |
+----------+----------+---------+------------------+----------------+
|        1 |     1001 |    2001 | 111              | (null)         |
|        1 |     1001 |    2001 | (null)           | 222            |
|        3 |     1003 |    2003 | 444              | (null)         |
+----------+----------+---------+------------------+----------------+

我想我需要使用pivot而不是union来获取同一行上的两个id。
你有没有遇到过这样的情况？任何继续的指示都会非常有用。
提前谢谢！
用于上述问题的ddl：

CREATE TABLE APPLE 
( APPLE_ID INTEGER,
 PHONE_ID INTEGER,
 IPAD_ID INTEGER);

 INSERT INTO APPLE VALUES (1,1001,2001);
 INSERT INTO APPLE VALUES (2,1002,2002);
 INSERT INTO APPLE VALUES (3,1003,2003);
 INSERT INTO APPLE VALUES (4,1004,2004);

 CREATE TABLE KEEY 
( KEEY_ID INTEGER,
 NAME VARCHAR2(50),
 DTL_ID INTEGER,
 DEVICE_ID INTEGER);

 INSERT INTO KEEY VALUES (1,'PHONE_ID',1001,111);
 INSERT INTO KEEY VALUES (2,'PHONE_ID',2001,111);
 INSERT INTO KEEY VALUES (3,'IPAD_ID',2001,222);
 INSERT INTO KEEY VALUES (4,'PHONE_ID',1003,444);
 INSERT INTO KEEY VALUES (5,'MAC_ID',367,333);