How to convert rows into column and join with table in oracle

Question

I am working on a sales project where there are two tables. one is sales_rev_asset and another is sales_lab.

Below are the table format:-

sales_rev_asset:- salesid year rev_ratio1 rev_ratio_2 ratio_3 asset_ratio1 asset_ratio_2 asset_ratio_3 10001 2016 30 12 56 78 89 90 10001 2017 13 56 87 33 95 28 10001 2018 98 84 53 62 24 48 10002 2016 33 55 62 69 78 10 10002 2017 62 17 12 14 7 9 10002 2018 28 83 45 16 35 50

sales_lab:- salesid ratio_name col1 col2 col3 10001 lab_ratio1 1 15 54 10001 lab_ratio2 3 54 41 10001 lab_ratio3 5 98 12 10002 lab_ratio1 2 74 23 10002 lab_ratio2 8 32 36 10002 lab_ratio3 9 19 65

But i want the data like below format:-

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 36 10002 2018 28 83 45 16 35 50 23 36 65 Please someone suggest me.How can i achieve the above format in oracle. I am using Oracle 11g.

Answer 1

From your question, you need use ROW_NUMBER function with windows function in subquery make the row number in sales_rev_asset table. then do unpivot in sales_lab,then join by the row number.

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);

Query 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 |

Answer 2

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;

Output:

   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

Answer 3

You need to pivot the sales_lab using this code:

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)
)

That will give you the output as you want it, then you can use a simple JOIN using salesid to display the data as you requested.