Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Orders (
    id NUMBER(10) PRIMARY KEY,
    order_number VARCHAR2(255),
    external_id NUMBER(10),
    version NUMBER(10),
    description VARCHAR2(255),
    create_ts TIMESTAMP
);
CREATE TABLE Partner (
    id NUMBER(10) PRIMARY KEY,
    order_id NUMBER(10),
    partner_code VARCHAR2(255),
    partner_name VARCHAR2(255),
    identifier VARCHAR2(255),
    type VARCHAR2(255),
    value NUMBER(10),
     FOREIGN KEY (order_id) REFERENCES Orders(id)
);
CREATE TABLE Fees (
    id NUMBER PRIMARY KEY,
    order_id NUMBER,
    identifier VARCHAR2(255),
    type VARCHAR2(255),
    value NUMBER,
    FOREIGN KEY (order_id) REFERENCES Orders(id)
);
CREATE TABLE order_attributes (
    id NUMBER PRIMARY KEY,
    order_number VARCHAR2(255),
    external_id NUMBER,
    partner_code VARCHAR2(255),
    identifier VARCHAR2(255),
    type VARCHAR2(255),
    value NUMBER
);
INSERT INTO Orders (id, order_number, external_id, version, description, create_ts)
VALUES 
  (1, 'ORD001', 1001, 1, 'External Id 1', TIMESTAMP '2024-05-05 10:00:00'),
  (2, 'ORD001', 1002, 1, 'External Id 2', TIMESTAMP '2024-05-05 10:00:00'),
  (3, 'ORD001', 1001, 2, 'External Id 1', TIMESTAMP '2024-05-05 11:00:00'),
  (4, 'ORD001', 1002, 2, 'External Id 2', TIMESTAMP '2024-05-05 11:00:00'),
  (5, 'ORD001', 1001, 3, 'External Id 1', TIMESTAMP '2024-05-05 12:00:00'),
  (6, 'ORD001', 1002, 3, 'External Id 2', TIMESTAMP '2024-05-05 12:00:00');
6 rows affected

-- Sample data for Partner table
INSERT INTO Partner (id, order_id, partner_code, partner_name, identifier, type, value)
VALUES 
    -- For identifier = INTERNAL
    (1, 5, 'P001', 'Partner 1', 'INTERNAL', 'A', 150),
    (2, 5, 'P001', 'Partner 1', 'INTERNAL', 'B', 170),
    (3, 5, 'P001', 'Partner 1', 'INTERNAL', 'C', 101),
    (4, 5, 'P001', 'Partner 1', 'INTERNAL', 'D', 105),
    (5, 5, 'P001', 'Partner 1', 'INTERNAL', 'E', 155),
    -- For identifier = EXTERNAL
    (6, 5, 'P001', 'Partner 1', 'EXTERNAL', 'A', 1600),
    (7, 5, 'P001', 'Partner 1', 'EXTERNAL', 'B', 1800),
    (8, 5, 'P001', 'Partner 1', 'EXTERNAL', 'C', 1100),
    (9, 5, 'P001', 'Partner 1', 'EXTERNAL', 'D', 1350),
    (10, 5, 'P001', 'Partner 1', 'EXTERNAL', 'E', 1600),
    -- For identifier = LOCAL
    (11, 5, 'P001', 'Partner 1', 'LOCAL', 'A', 17000),
    (12, 5, 'P001', 'Partner 1', 'LOCAL', 'B', 19000),
    (13, 5, 'P001', 'Partner 1', 'LOCAL', 'C', 12000),
    (14, 5, 'P001', 'Partner 1', 'LOCAL', 'D', 14500),
    (15, 5, 'P001', 'Partner 1', 'LOCAL', 'E', 17000),

    (16, 6, 'P002', 'Partner 2', 'INTERNAL', 'A', 250),
    (17, 6, 'P002', 'Partner 2', 'INTERNAL', 'B', 207),
    (18, 6, 'P002', 'Partner 2', 'INTERNAL', 'C', 201),
    (19, 6, 'P002', 'Partner 2', 'INTERNAL', 'D', 205),
    (20, 6, 'P002', 'Partner 2', 'INTERNAL', 'E', 205),
    -- For identifier = EXTERNAL
    (21, 6, 'P002', 'Partner 2', 'EXTERNAL', 'A', 2600),
    (22, 6, 'P002', 'Partner 2', 'EXTERNAL', 'B', 2800),
    (23, 6, 'P002', 'Partner 2', 'EXTERNAL', 'C', 2100),
    (24, 6, 'P002', 'Partner 2', 'EXTERNAL', 'D', 2350),
    (25, 6, 'P002', 'Partner 2', 'EXTERNAL', 'E', 2600),
    -- For identifier = LOCAL
    (26, 6, 'P002', 'Partner 2', 'LOCAL', 'A', 27000),
    (27, 6, 'P002', 'Partner 2', 'LOCAL', 'B', 29000),
    (28, 6, 'P002', 'Partner 2', 'LOCAL', 'C', 22000),
    (29, 6, 'P002', 'Partner 2', 'LOCAL', 'D', 24500),
    (30, 6, 'P002', 'Partner 2', 'LOCAL', 'E', 27000);
30 rows affected

INSERT INTO Fees (id, order_id, identifier, type, value)
VALUES 
    -- For identifier = EXPENSE
    (1, 5, 'EXPENSE', 'F1', 50),
    (2, 5, 'EXPENSE', 'F2', 75),
    (3, 5, 'EXPENSE', 'F3', 10),
    (4, 5, 'EXPENSE', 'F4', 12),
    -- Fr identifier = BILLED
    (5, 5, 'BILLED', 'F1', 60),
    (6, 5, 'BILLED', 'F2', 80),
    (7, 5, 'BILLED', 'F3', 11),
    (8, 5, 'BILLED', 'F4', 13),
    -- For identifier = SETTLED
    (9, 5, 'SETTLED', 'F1', 70),
    (10, 5, 'SETTLED', 'F2', 90),
    (11, 5, 'SETTLED', 'F3', 17),
    (12, 5, 'SETTLED', 'F4', 14),
    
  
  (21, 6, 'EXPENSE', 'F1', 850),
    (22, 6, 'EXPENSE', 'F2', 875),
    (23, 6, 'EXPENSE', 'F3', 810),
    (24, 6, 'EXPENSE', 'F4', 812),
    -- Fr identifier = BILLED
    (25, 6, 'BILLED', 'F1', 860),
    (26, 6, 'BILLED', 'F2', 880),
    (27, 6, 'BILLED', 'F3', 811),
    (28, 6, 'BILLED', 'F4', 813),
    -- For identifier = SETTLED
    (29, 6, 'SETTLED', 'F1', 870),
    (30, 6, 'SETTLED', 'F2', 880),
    (31, 6, 'SETTLED', 'F3', 817),
    (32, 6, 'SETTLED', 'F4', 814)
24 rows affected

-- Sample data for order_attributes table
INSERT INTO order_attributes (id, order_number, external_id, partner_code, identifier, type, value)
VALUES 
    ('1', 'ORD001', '1001', 'P001', 'CALCULATED', 'X', 123),
    ('2', 'ORD001', '1001', 'P001', 'CALCULATED', 'Y', 234),
    ('3', 'ORD001', '1001', 'P001', 'CALCULATED', 'Z', 23432),
    ('4', 'ORD001', '1001', 'P001', 'EDITED', 'X', 4665),
    ('5', 'ORD001', '1001', 'P001', 'EDITED', 'Y', 4557),
    ('6', 'ORD001', '1001', 'P001', 'EDITED', 'Z', 567567),
    ('7', 'ORD001', '1002', 'P001', 'CALCULATED', 'X', 3423),
    ('8', 'ORD001', '1002', 'P001', 'CALCULATED', 'Y', 434),
    ('9', 'ORD001', '1002', 'P001', 'CALCULATED', 'Z', 9897),
    ('10', 'ORD001', '1002', 'P001', 'EDITED', 'X', 6767),
    ('11', 'ORD001', '1002', 'P001', 'EDITED', 'Y', 678),
    ('12', 'ORD001', '1002', 'P001', 'EDITED', 'Z', 7887)
12 rows affected

SELECT o.order_number,
       o.external_id,
       p.partner_code,
       o.version,
       p.partner_name,
       o.create_ts,
       o.description,
       COALESCE(a.calculated_x_value, p.a_value) AS value_a,
       COALESCE(a.edited_x_value, p.b_value) AS value_b,
       p.c_value AS value_c,
       p.d_value AS value_d,
       p.e_value AS value_e,
       f.f1_value,
       f.f2_value,
       f.f3_value,
       f.f4_value
FROM   ( SELECT o.*,
                RANK() OVER (
                  PARTITION BY order_number, external_id
                  ORDER BY create_ts DESC, version DESC
                ) AS rnk
         FROM   Orders o
       ) o
       LEFT OUTER JOIN (
         SELECT order_id,
                partner_code,
                MAX(partner_name) AS partner_name,
                MAX(CASE WHEN identifier = 'INTERNAL' AND type = 'A' THEN value END)
                  AS a_value,
                MAX(CASE WHEN identifier = 'INTERNAL' AND type = 'B' THEN value END)
                  AS b_value,
                MAX(CASE WHEN identifier = 'EXTERNAL' AND type = 'C' THEN value END)
                  AS c_value,
                MAX(CASE WHEN identifier = 'LOCAL' AND type = 'D' THEN value END)
                  AS d_value,
                MAX(CASE WHEN identifier = 'LOCAL' AND type = 'E' THEN value END)
                  AS e_value
         FROM   partner
         GROUP BY order_id, partner_code
       ) p
       ON o.id = p.order_id
       LEFT JOIN (
         SELECT order_id,
                MAX(CASE WHEN identifier = 'EXPENSE' AND type = 'F1' THEN value END)
                  AS f1_value,
                MAX(CASE WHEN identifier = 'EXPENSE' AND type = 'F2' THEN value END)
                  AS f2_value,
                MAX(CASE WHEN identifier = 'BILLED' AND type = 'F3' THEN value END)
                  AS f3_value,
                MAX(CASE WHEN identifier = 'BILLED' AND type = 'F4' THEN value END)
                  AS f4_value
         FROM   Fees
         GROUP BY
                order_id
       ) f
       ON o.id = f.order_id
       LEFT JOIN (
         SELECT order_number,
                external_id,
                partner_code,
                MAX(CASE WHEN identifier = 'CALCULATED' AND type = 'X' THEN value END)
                  AS calculated_x_value,
                MAX(CASE WHEN identifier = 'EDITED' AND type = 'X' THEN value END)
                  AS edited_x_value
         FROM   order_attributes
         GROUP BY
                order_number,
                external_id,
                partner_code
       ) a
       ON o.order_number = a.order_number 
          AND o.external_id = a.external_id 
          AND p.partner_code = a.partner_code
WHERE  o.rnk = 1
AND    o.order_number='ORD001'


ORDER_NUMBER
EXTERNAL_ID
PARTNER_CODE
VERSION
PARTNER_NAME
CREATE_TS
DESCRIPTION
VALUE_A
VALUE_B
VALUE_C
VALUE_D
VALUE_E
F1_VALUE
F2_VALUE
F3_VALUE
F4_VALUE


ORD001
1001
P001
3
Partner 1
05-MAY-24 12.00.00.000000
External Id 1
123
4665
1100
14500
17000
50
75
11
13

ORD001
1002
P002
3
Partner 2
05-MAY-24 12.00.00.000000
External Id 2
250
207
2100
24500
27000
850
875
811
813


WITH ranked_orders AS (
  SELECT o.*,
         RANK() OVER (
           PARTITION BY order_number, external_id
           ORDER BY create_ts DESC, version DESC
         ) AS rnk
  FROM   Orders o
),
pivoted_partners AS (
  SELECT order_id,
         partner_code,
         MAX(partner_name) AS partner_name,
         MAX(CASE WHEN identifier = 'INTERNAL' AND type = 'A' THEN value END)
           AS a_value,
         MAX(CASE WHEN identifier = 'INTERNAL' AND type = 'B' THEN value END)
           AS b_value,
         MAX(CASE WHEN identifier = 'EXTERNAL' AND type = 'C' THEN value END)
           AS c_value,
         MAX(CASE WHEN identifier = 'LOCAL' AND type = 'D' THEN value END)
           AS d_value,
         MAX(CASE WHEN identifier = 'LOCAL' AND type = 'E' THEN value END)
           AS e_value
  FROM   partner
  GROUP BY order_id, partner_code
),
pivoted_fees AS (
  SELECT order_id,
         MAX(CASE WHEN identifier = 'EXPENSE' AND type = 'F1' THEN value END)
           AS f1_value,
         MAX(CASE WHEN identifier = 'EXPENSE' AND type = 'F2' THEN value END)
           AS f2_value,
         MAX(CASE WHEN identifier = 'BILLED' AND type = 'F3' THEN value END)
           AS f3_value,
         MAX(CASE WHEN identifier = 'BILLED' AND type = 'F4' THEN value END)
           AS f4_value
  FROM   Fees
  GROUP BY
         order_id
),
pivoted_attributes AS (
  SELECT order_number,
         external_id,
         partner_code,
         MAX(CASE WHEN identifier = 'CALCULATED' AND type = 'X' THEN value END)
           AS calculated_x_value,
         MAX(CASE WHEN identifier = 'EDITED' AND type = 'X' THEN value END)
           AS edited_x_value
  FROM   order_attributes
  GROUP BY
         order_number,
         external_id,
         partner_code
)
SELECT o.order_number,
       o.external_id,
       p.partner_code,
       o.version,
       p.partner_name,
       o.create_ts,
       o.description,
       COALESCE(a.calculated_x_value, p.a_value) AS value_a,
       COALESCE(a.edited_x_value, p.b_value) AS value_b,
       p.c_value AS value_c,
       p.d_value AS value_d,
       p.e_value AS value_e,
       f.f1_value,
       f.f2_value,
       f.f3_value,
       f.f4_value
FROM   ranked_orders o
       LEFT OUTER JOIN pivoted_partners p
       ON o.id = p.order_id
       LEFT JOIN pivoted_fees f
       ON o.id = f.order_id
       LEFT JOIN pivoted_attributes a
       ON o.order_number = a.order_number 
          AND o.external_id = a.external_id 
          AND p.partner_code = a.partner_code
WHERE  o.rnk = 1
AND    o.order_number='ORD001'


ORDER_NUMBER
EXTERNAL_ID
PARTNER_CODE
VERSION
PARTNER_NAME
CREATE_TS
DESCRIPTION
VALUE_A
VALUE_B
VALUE_C
VALUE_D
VALUE_E
F1_VALUE
F2_VALUE
F3_VALUE
F4_VALUE


ORD001
1001
P001
3
Partner 1
05-MAY-24 12.00.00.000000
External Id 1
123
4665
1100
14500
17000
50
75
11
13

ORD001
1002
P002
3
Partner 2
05-MAY-24 12.00.00.000000
External Id 2
250
207
2100
24500
27000
850
875
811
813
ORDER_NUMBER	EXTERNAL_ID	PARTNER_CODE	VERSION	PARTNER_NAME	CREATE_TS	DESCRIPTION	VALUE_A	VALUE_B	VALUE_C	VALUE_D	VALUE_E	F1_VALUE	F2_VALUE	F3_VALUE	F4_VALUE
ORD001	1001	P001	3	Partner 1	05-MAY-24 12.00.00.000000	External Id 1	123	4665	1100	14500	17000	50	75	11	13
ORD001	1002	P002	3	Partner 2	05-MAY-24 12.00.00.000000	External Id 2	250	207	2100	24500	27000	850	875	811	813