时间戳

首页 > 解决方案 > 甲骨文：我怎样才能对特定键具有动态基数的 EAV 表进行透视？

sql - 甲骨文：我怎样才能对特定键具有动态基数的 EAV 表进行透视？

问题描述

我在 Oracle 中有以下实体属性值 (EAV) 表：

| 身份证 | 钥匙 | 价值 |
|----|-------------|--------------|
| 1 | 电话号码_1 | 111-111-1111 |
| 1 | phone_num_2 | 222-222-2222 |
| 1 | 联系方式_1 | 朋友 |
| 1 | 联系方式_2 | 家庭 |
| 1 | 名字 | 迈克 |
| 1 | 姓氏 | 史密斯 |
| 2 | 电话号码_1 | 333-333-3333 |
| 2 | phone_num_2 | 444-444-4444 |
| 2 | 联系方式_1 | 家庭 |
| 2 | 联系方式_2 | 朋友 |
| 2 | 名字 | 约翰 |
| 2 | 姓氏 | 亚当斯 |
| 3 | 电话号码_1 | 555-555-5555 |
| 3 | phone_num_2 | 666-666-6666 |
| 3 | phone_num_3 | 777-777-7777 |
| 3 | 联系方式_1 | 工作 |
| 3 | 联系方式_2 | 家庭 |
| 3 | 联系方式_3 | 朋友 |
| 3 | 名字 | 莫娜 |
| 3 | 姓氏 | 丽莎 |

请注意，某些键已编入索引，因此与其他索引键有关联。例如，phone_num_1 将与contact_1 关联。

注意：索引的数量没有硬性限制。可以有10个、20个、甚至50个phone_num_*，但保证每个phone_num_N都有一个对应的contact_N

这是我想要的结果：

| 身份证 | 电话号码 | 联系方式 | 名字 | 姓氏 |
|----|-------------|---------|------------|------ -----|
| 1 | 111-111-1111 | 朋友 | 迈克 | 史密斯 |
| 1 | 222-222-2222 | 家庭 | 迈克 | 史密斯 |
| 2 | 333-333-3333 | 家庭 | 约翰 | 亚当斯 |
| 2 | 444-444-4444 | 朋友 | 约翰 | 亚当斯 |
| 3 | 555-555-5555 | 工作 | 莫娜 | 丽莎 |
| 3 | 666-666-6666 | 家庭 | 莫娜 | 丽莎 |
| 3 | 777-777-7777 | 朋友 | 莫娜 | 丽莎 |

我试过/看过什么：

我研究了 Oracle 的枢轴功能；但是，我不相信这可以解决我的问题，因为我没有固定数量的属性，我想以此为中心。我看过这些帖子： SQL Query to return multiple key value pairs from a single table in one row

将行透视到没有聚合的列

问题：

我想要完全用 SQL 来完成吗？如果是这样，怎么办？如果不是，请解释原因。

任何帮助都非常感谢，这里是帮助您入门的表：

with
    table_1 ( id, key, value ) as (
        select 1,'phone_num_1','111-111-1111' from dual union all
        select 1,'phone_num_2','222-222-2222' from dual union all
        select 1,'contact_1','friend' from dual union all
        select 1,'contact_2','family' from dual union all
        select 1,'first_name','mike' from dual union all
        select 1,'last_name','smith' from dual union all
        select 2,'phone_num_1','333-333-3333' from dual union all
        select 2,'phone_num_2','444-444-4444' from dual union all
        select 2,'contact_1','family' from dual union all
        select 2,'contact_2','friend' from dual union all
        select 2,'first_name','john' from dual union all
        select 2,'last_name','adams' from dual union all
        select 3,'phone_num_1','555-555-5555' from dual union all
        select 3,'phone_num_2','666-666-6666' from dual union all
        select 3,'phone_num_3','777-777-7777' from dual union all
        select 3,'contact_1','work' from dual union all
        select 3,'contact_2','family' from dual union all
        select 3,'contact_3','friend' from dual union all
        select 3,'first_name','mona' from dual union all
        select 3,'last_name','lisa' from dual
     )
select * from table_1;

标签： sqloraclepivotentity-attribute-value

解决方案

这很难看，但我认为可以满足您的需要

select t1.* , t2.value, t3.n, t3.f
from table_1 t1
inner join table_1 t2 on t1.id = t2.id and REPLACE(t1.key, 'phone_num_', '') = REPLACE(t2.key, 'contact_', '')
inner join (
    select ID, min(case when Key = 'first_name' then Value end) as n, min(case when Key = 'last_name' then Value end) as f
    from table_1
    group by ID
) t3 on t1.id = t3.id
where
t1.Key not in('first_name','last_name')

推荐阅读