如何将一个表的水平值绑定到oracle数据库中另一个表的垂直值 [英] How to bind horizontal values of a table to a vertical values of another table in oracle database

问题描述

我有两张桌子.

以属性开头的列根据部门而变化.属性描述在这里

The columns start with attributes are change based on department. the description of attributes are here

我的要求是根据如下表根据部门获取每个属性及其主键的值.

My requirement is to get the values of each attributes with its primary key based on the department as table bellow.

老实说，我在我的程序中遇到了这个问题.我无权更改表，也没有通用的唯一键列.如果有人能给我提供建议，我将不胜感激.

Honestly i am stuck on this problem in my program. I have no permission to change the tables and there is no common unique key column.i would appreciate if anyone could provide me a suggestion.

推荐答案

with a as (
    select a.*, row_number() over (partition by department order by attributeID) rn
      from attributes a),
  e as (
    select employeeId, department, attribute1, 1 rn from employees union all
    select employeeId, department, attribute2, 2 rn from employees union all
    select employeeId, department, attribute3, 3 rn from employees
  )
select e.employeeId, a.attributeid, e.department, a.attribute, a.meaning, 
       e.attribute1 as value 
  from e join a on a.department=e.department and a.rn=e.rn 
  order by e.employeeId, a.attributeid

测试数据和输出:

create table employees (employeeID number(3), name varchar2(10), department varchar2(5), age number(3), attribute1 varchar2(10), attribute2 varchar2(10), attribute3 varchar2(10));
insert into employees values (1, 'john', 'IT', 22, 'attr1val1', 'attr2val2',  null);
insert into employees values (2, 'jane', 'HR', 32, 'attr1val3', 'attr2val4',  'attr3val5');
insert into employees values (3, 'joe',  'HR', 23, 'attr1val6', 'attr2val7',  'attr3val8');
insert into employees values (4, 'jack', 'IT', 45, 'attr1val9', 'attr2val10', null);

create table attributes (attributeID number(3), department varchar2(10), attribute varchar2(10), meaning varchar2(10));
insert into attributes values (1, 'IT', 'attribute1', 'laptoptype');
insert into attributes values (2, 'IT', 'attribute2', 'networkloc');
insert into attributes values (3, 'HR', 'attribute1', 'location');
insert into attributes values (4, 'HR', 'attribute2', 'position');
insert into attributes values (5, 'HR', 'attribute3', 'allocation');

EMPLOYEEID ATTRIBUTEID DEPARTMENT ATTRIBUTE  MEANING    VALUE
---------- ----------- ---------- ---------- ---------- ----------
         1           1 IT         attribute1 laptoptype attr1val1
         1           2 IT         attribute2 networkloc attr2val2
         2           3 HR         attribute1 location   attr1val3
         2           4 HR         attribute2 position   attr2val4
         2           5 HR         attribute3 allocation attr3val5
         3           3 HR         attribute1 location   attr1val6
         3           4 HR         attribute2 position   attr2val7
         3           5 HR         attribute3 allocation attr3val8
         4           1 IT         attribute1 laptoptype attr1val9
         4           2 IT         attribute2 networkloc attr2val10

<小时>

编辑:说明

在回答中我使用了 with条款只是为了将解决方案分成可读的步骤.如果是，您可以将它们移动到主查询的 from 子句中对你来说更舒服.无论如何:子查询 a 从表 attributes 读取数据并添加行数，所以对于每个部门，它们总是从 1 开始编号.我使用了 row_number() 为此.子查询 e 联合(所有)必需的属性和数字他们相应地.然后在主连接中使用在两个子查询中生成的数字:a.department=e.department and a.rn=e.rn.

In answer I used with clause just to divide solution into readable steps. You can move them into from clause of main query if it is more comfortable for you. Anyway: subquery a reads data from table attributes and adds number for rows, so for each department they are allways numbered from 1. I used row_number() for that. Subquery e unions (all) required attributes and numbers them accordingly. Numbers generated in both subqueries are then used in main join: a.department=e.department and a.rn=e.rn.

备选方案 1 - 如果您使用的是 Oracle 11g，则可以使用 unpivot.查看子查询生成的内容，以及它如何与 attributes 表连接:

Alternative 1 - if you are using Oracle 11g you could use the unpivot. See what is generated by subquery, and how it is joined with attributes table:

with e as (
    select employeeId, name, department, attribute, value from employees
      unpivot (value for attribute in ("ATTRIBUTE1", "ATTRIBUTE2", "ATTRIBUTE3"))  
  )
select e.employeeId, a.attributeid, e.department, a.attribute, 
       a.meaning, e.value 
  from e join attributes a on a.department=e.department 
                          and lower(a.attribute)=lower(e.attribute)
  order by e.employeeId, a.attributeid;

<小时>

替代方案 2 - 带有分层子查询生成器(子查询 r)，由 connect by 简单地从 1、2、3 创建数字，然后与 employees和适当的属性在 case 子句中作为值附加.休息的方式与原始答案类似.

---

Alternative 2 - with hierarchical subquery generator (subquery r), realised by connect by which simple creates numbers from 1, 2, 3 which are next joined with employees and proper attribute is attached as value in case clause. Rest is made in similiar way like in original answer.

with a as (
    select a.*, row_number() over (partition by department order by attributeID) rn
      from attributes a),
  r as (select level rn from dual connect by level<=3),
  e as (
    select employeeId, department, rn,
           case when r.rn = 1 then attribute1
                when r.rn = 2 then attribute2
                when r.rn = 3 then attribute3
           end value
      from employees cross join r
  )
select e.employeeId, a.attributeid, e.department, a.attribute,
       a.meaning, e.value
  from e join a on a.department=e.department and a.rn=e.rn
  order by e.employeeId, a.attributeid

所有三个版本都给了我相同的输出.我还在具有 100k 行的类似表上测试了第一个选项，并在几秒钟内获得输出(5 个属性).请测试所有解决方案并尝试理解它们.如果你可以使用 unpivot 版本，我更喜欢这个.抱歉延迟解释和任何语言错误.

All three versions gave me the same output. I also tested first option on similiar table with 100k rows and get output in few seconds (for 5 attributes). Please test all solutions and try to understand them. If you can use unpivot version I would prefer this. Sorry for delayed explanation and any language mistakes.

这篇关于如何将一个表的水平值绑定到oracle数据库中另一个表的垂直值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！