Pivot 结果在 Oracle 中

Question

我有四个表(甲骨文(：

party
----------------
key
person
----------------
party_key | name
organization
----------------
party_key | name
id
----------------
party_key | id (varchar)

在"id"表中，每个组织有多个(超过1个(id，每个人都有一个(不是我的业务/数据模型设计 - 无法控制这一点(。

所以这个 sql 查询：

SELECT pers.name as person_name, org.name as org_name, id_pers.id as person_id, id_org.id as org_id
FROM party part
INNER JOIN person pers ON pers.party_key = part.key
INNER JOIN organization org ON org.party_key = part.key
INNER JOIN id id_pers ON pers.party_key = id_pers.party_key
INNER JOIN id id_org ON pers.party_key = id_org.party_key

产生这个：

person_name |   org_name | person_id | org_id
John | whitehouse |     00005 |   0001
John | whitehouse |     00005 |   0002
Samantha | whitehouse |     00007 |   0001
Samantha | whitehouse |     00007 |   0002
John |    library |     00005 |   0008
John |    library |     00005 |   0009
Samantha |    library |     00007 |   0008
Samantha |    library |     00007 |   0009

但是我希望SQL查询产生这样的东西：

person_name |   org_name | person_id | org_id1 | org_id2
John | whitehouse |     00005 |    0001 |    0002
Samantha | whitehouse |     00007 |    0001 |    0002
John |    library |     00005 |    0008 |    0009
Samantha |    library |     00007 |    0008 |    0009

我认为解决方案涉及pivot但我不确定如何执行它。

Answer 1

使用最小值和最大值以及分组依据：

select person_name, org_name,person_id, min(org_id), max(org_id)
from 
(SELECT pers.name as person_name, org.name as org_name, id_pers.id as person_id, id_org.id as org_id
FROM party part
INNER JOIN person pers ON pers.party_key = part.key
INNER JOIN organization org ON org.party_key = part.key
INNER JOIN id id_pers ON pers.party_key = id_pers.party_key
INNER JOIN id id_org ON pers.party_key = id_org.party_key)a
group by person_name, org_name,person_id

Answer 2

我不确定您的表结构并始终加入party_key工作......但以下是使用PIVOT的方法(您首先必须使用ROW_NUMBER分析函数为每个组织 ID 分配一个行号(：

SQL 小提琴

Oracle 11g R2 架构设置：

CREATE TABLE party ( key ) AS
SELECT 1 FROM DUAL;
CREATE TABLE person (party_key, name ) AS
SELECT 1, 'John'     FROM DUAL UNION ALL
SELECT 1, 'Samantha' FROM DUAL;
CREATE TABLE organization ( party_key, name ) AS
SELECT 1, 'Whitehouse' FROM DUAL UNION ALL
SELECT 1, 'Library'    FROM DUAL;

CREATE TABLE id ( party_key, id ) AS
SELECT 1, '0001' FROM DUAL UNION ALL
SELECT 1, '0002' FROM DUAL;

查询 1：

SELECT *
FROM   (
SELECT pers.name as person_name,
org.name as org_name,
id_pers.id as person_id,
id_org.id as org_id,
ROW_NUMBER() OVER (
PARTITION BY pers.name, org.name, id_pers.id
ORDER BY id_org.id
) AS rn
FROM   party part
INNER JOIN person pers ON pers.party_key = part.key
INNER JOIN organization org ON org.party_key = part.key
INNER JOIN id id_pers ON pers.party_key = id_pers.party_key
INNER JOIN id id_org ON pers.party_key = id_org.party_key
)
PIVOT ( MAX( org_id ) FOR rn IN (
1 AS org_id1,
2 AS org_id2
) )

结果：

| PERSON_NAME |   ORG_NAME | PERSON_ID | ORG_ID1 | ORG_ID2 |
|-------------|------------|-----------|---------|---------|
|        John |    Library |      0001 |    0001 |    0002 |
|        John |    Library |      0002 |    0001 |    0002 |
|        John | Whitehouse |      0001 |    0001 |    0002 |
|        John | Whitehouse |      0002 |    0001 |    0002 |
|    Samantha |    Library |      0001 |    0001 |    0002 |
|    Samantha |    Library |      0002 |    0001 |    0002 |
|    Samantha | Whitehouse |      0001 |    0001 |    0002 |
|    Samantha | Whitehouse |      0002 |    0001 |    0002 |