建立一个表A，
id name
1 张三
2 李四
建立一个表B，
id job salary
1 001 10
1 002 20
2 002 20
2 003 30
现在想联合表AB得到如下结果：
id name job1 salary1 job2 salary2
1 张三 001 10 002 20
2 李四 002 20 003 30
请问用sql怎么实现呢？
------解决思路----------------------
WITH T AS (
SELECT B.*,ROW_NUMBER()OVER(PARTITION BY ID,ORDER BY JOB) RN
FROM B
)
SELECT A.ID,MAX(A.NAME) NAME,
MAX(DECODE(B.RN,1,B.JOB)) JOB1,
MAX(DECODE(B.RN,1,B.salary)) salary1,
MAX(DECODE(B.RN,2,B.JOB)) JOB2,
MAX(DECODE(B.RN,2,B.salary)) salary2
FROM A,B
WHERE A.ID=B.ID
GROUP BY A.ID
------解决思路----------------------
只能固定列，如果还有job3还要加列
仅供参考：
select t1.id,
t1.name,
(select min(t2.job)
from (select b.*,
dense_rank() over(partition by id order by job) rn
from b) t2
where t2.rn = 1
and t2.id = t1.id) job1,
(select sum(t2.salary)
from (select b.*,
dense_rank() over(partition by id order by job) rn
from b) t2
where t2.rn = 1
and t2.id = t1.id) salary1,
(select min(t2.job)
from (select b.*,
dense_rank() over(partition by id order by job) rn
from b) t2
where t2.rn = 2
and t2.id = t1.id) job2,
(select sum(t2.salary)
from (select b.*,
dense_rank() over(partition by id order by job) rn
from b) t2
where t2.rn = 2
and t2.id = t1.id) salary2
from a t1;