CREATE TABLE a as (select 1 id ,'項目一' XMMC,'XMONE' CODE,1 RYID FROM DUAL
UNION
select 2 id ,'項目二' XMMC,'XMTWO' CODE,1 RYID FROM DUAL
UNION
select 3 id ,'項目三' XMMC,'XMTHREE' CODE,1 RYID FROM DUAL
UNION
select 4 id ,'項目一' XMMC,'XMONE' CODE,2 RYID FROM DUAL
)
WITH B AS (SELECT 1 ID,'張三' NAME FROM DUAL
UNION
SELECT 2 ID,'李四' NAME FROM DUAL
)
SELECT C.RYID,
B.NAME,
MAX(DECODE(RN, 1, XMMC, NULL)) XMONE,
MAX(DECODE(RN, 2, XMMC, NULL)) XMTWO,
MAX(DECODE(RN, 3, XMMC, NULL)) XMTHREE
FROM (SELECT RYID,
XMMC,
CODE,
ROW_NUMBER() OVER(PARTITION BY RYID ORDER BY RYID) RN --以RYID分组后内部的排序
FROM A) C,
B
WHERE C.RYID = B.ID
GROUP BY C.RYID, B.NAME
ORDER BY C.RYID
-- RYID NAME XMONE XMTWO XMTHREE
---------- ---- ------ ------ -------
-- 1 張三 項目一 項目二 項目三
-- 2 李四 項目一