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 李四 項目一????????