oracle中如何将行转换成列并与表联接

从你的问题来看,你需要利用 ROW_NUMBER 功能 windows函数 在子查询中使行号 sales_rev_asset 桌子。那就去吧 unpivot 在里面 sales_lab ,然后 join 按行号。

CREATE TABLE sales_rev_asset(
  salesid        INT,
  year           INT,
  rev_ratio1     INT,
  rev_ratio_2    INT,
  ratio_3        INT,
  asset_ratio1   INT,
  asset_ratio_2  INT,
  asset_ratio_3  INT
);


INSERT INTO    sales_rev_asset VALUES (10001,2016,30,12,56,78,89,90);
INSERT INTO    sales_rev_asset VALUES (10001,2017,13,56,87,33,95,28);
INSERT INTO    sales_rev_asset VALUES (10001,2018,98,84,53,62,24,48);
INSERT INTO    sales_rev_asset VALUES (10002,2016,33,55,62,69,78,10);
INSERT INTO    sales_rev_asset VALUES (10002,2017,62,17,12,14,7 ,9);
INSERT INTO    sales_rev_asset VALUES (10002,2018,28,83,45,16,35,50);


CREATE TABLE sales_lab(
  salesid     INT,
  ratio_name  VARCHAR(50),
  col1     INT,
  col2    INT,
  col3     INT
);

INSERT INTO sales_lab VALUES (10001,'lab_ratio1',1,15,54);
INSERT INTO sales_lab VALUES (10001,'lab_ratio2',3,54,41);
INSERT INTO sales_lab VALUES (10001,'lab_ratio3',5,98,12);
INSERT INTO sales_lab VALUES (10002,'lab_ratio1',2,74,23);
INSERT INTO sales_lab VALUES (10002,'lab_ratio2',8,32,36);
INSERT INTO sales_lab VALUES (10002,'lab_ratio3',9,19,65);

问题1 :

with cte as (
  select t.*,ROW_NUMBER() OVER(PARTITION BY salesid ORDER BY salesid) rn 
  from sales_lab t
),unpivot_CTE as (
   select t.SALESID,
           1 RN,
          (Case when t.RATIO_NAME = 'lab_ratio1' and rn = 1  then col1 end) AS lab_ratio1,
          (Case when t.RATIO_NAME = 'lab_ratio2' and rn = 2  then col1 end) AS lab_ratio2,
          (Case when t.RATIO_NAME = 'lab_ratio3' and rn = 3  then col1 end) AS lab_ratio3
    from cte t
    UNION ALL
    select t.SALESID,
           2 RN,
           (Case when t.RATIO_NAME = 'lab_ratio1' and rn = 1  then col2 end),
           (Case when t.RATIO_NAME = 'lab_ratio2' and rn = 2  then col2 end),
           (Case when t.RATIO_NAME = 'lab_ratio3' and rn = 3  then col2 end)
    from cte t
    UNION ALL
    select t.SALESID,
           3 RN,
           (Case when t.RATIO_NAME = 'lab_ratio1' and rn = 1  then col3 end),
           (Case when t.RATIO_NAME = 'lab_ratio2' and rn = 2  then col3 end),
           (Case when t.RATIO_NAME = 'lab_ratio3' and rn = 3  then col3 end)
    from cte t
)
select t1.*,t2.lab_ratio1,t2.lab_ratio2,t2.lab_ratio3
from (
  select t.*,ROW_NUMBER() OVER(PARTITION BY salesid ORDER BY year) rn 
  from sales_rev_asset t
) t1
INNER JOIN (
   select 
      SALESID,
      rn,
      MAX(lab_ratio1)  lab_ratio1,
      MAX(lab_ratio2)  lab_ratio2,
      MAX(lab_ratio3)  lab_ratio3
   from unpivot_CTE
   group by SALESID,rn
) t2 ON t1.salesid = t2.salesid and t1.rn = t2.rn
ORDER BY t1.SALESID,t1.year

Results :

| SALESID | YEAR | REV_RATIO1 | REV_RATIO_2 | RATIO_3 | ASSET_RATIO1 | ASSET_RATIO_2 | ASSET_RATIO_3 | RN | LAB_RATIO1 | LAB_RATIO2 | LAB_RATIO3 |
|---------|------|------------|-------------|---------|--------------|---------------|---------------|----|------------|------------|------------|
|   10001 | 2016 |         30 |          12 |      56 |           78 |            89 |            90 |  1 |          1 |          3 |          5 |
|   10001 | 2017 |         13 |          56 |      87 |           33 |            95 |            28 |  2 |         15 |         54 |         98 |
|   10001 | 2018 |         98 |          84 |      53 |           62 |            24 |            48 |  3 |         54 |         41 |         12 |
|   10002 | 2016 |         33 |          55 |      62 |           69 |            78 |            10 |  1 |          2 |          8 |          9 |
|   10002 | 2017 |         62 |          17 |      12 |           14 |             7 |             9 |  2 |         74 |         32 |         19 |
|   10002 | 2018 |         28 |          83 |      45 |           16 |            35 |            50 |  3 |         23 |         36 |         65 |

你需要转动 sales_lab 使用此代码:

SELECT * FROM
(
  SELECT salesid, ratio_name, col1
  FROM sales_tab
  WHERE conditions
)
PIVOT 
(
  MAX(col1)
  FOR ratio_name
  IN ( lab_ratio1, lab_ratio2, lab_ratio3)
)

这将提供您想要的输出,然后您可以使用一个使用salesid的简单连接来显示您请求的数据。

create table pivoted as
SELECT * FROM
(
  SELECT salesid,2016 year, ratio_name, col1
  FROM sales_lab
)
PIVOT 
(
  MAX(col1)
  FOR ratio_name
  IN ( 'lab_ratio1' lab_ratio1, 'lab_ratio2' lab_ratio2, 'lab_ratio3' lab_ratio3)
)
union
SELECT * FROM
(
  SELECT salesid,2017 year, ratio_name, col2
  FROM sales_lab
)
PIVOT 
(
  MAX(col2)
  FOR ratio_name
  IN ( 'lab_ratio1' lab_ratio1, 'lab_ratio2' lab_ratio2, 'lab_ratio3' lab_ratio3)
)
union
SELECT * FROM
(
  SELECT salesid,2018 year, ratio_name, col3
  FROM sales_lab
)
PIVOT 
(
  MAX(col3)
  FOR ratio_name
  IN ( 'lab_ratio1' lab_ratio1, 'lab_ratio2' lab_ratio2, 'lab_ratio3' lab_ratio3)
);

select
s.salesid,
s.year,
s.rev_ratio1,
s.rev_ratio_2,
s.ratio_3,
s.asset_ratio1,
s.asset_ratio_2,
s.asset_ratio_3,
p.lab_ratio1,
p.lab_ratio2,
p.lab_ratio3
from 
sales_rev_asset s
join
pivoted p
on
  s.salesid = p.salesid and
  s.year = p.year
order by
s.salesid,
s.year;

输出:

   SALESID       YEAR REV_RATIO1 REV_RATIO_2    RATIO_3 ASSET_RATIO1 ASSET_RATIO_2 ASSET_RATIO_3 LAB_RATIO1 LAB_RATIO2 LAB_RATIO3
---------- ---------- ---------- ----------- ---------- ------------ ------------- ------------- ---------- ---------- ----------
     10001       2016         30          12         56           78            89            90          1          3          5
     10001       2017         13          56         87           33            95            28         15         54         98
     10001       2018         98          84         53           62            24            48         54         41         12
     10002       2016         33          55         62           69            78            10          2          8          9
     10002       2017         62          17         12           14             7             9         74         32         19
     10002       2018         28          83         45           16            35            50         23         36         65