SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 
 Oct  8 2022 05:58:25 
 Copyright (C) 2022 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE table_1 (
    region VARCHAR(255),
    period VARCHAR(255),
    category VARCHAR(255),
    some_value INT
);

INSERT INTO table_1 (region, period, category, some_value)
VALUES ('region1', '1', 'a', 20),
       ('region1', '1', 'b', 10),
       ('region2', '1', 'c', 30),
       ('region2', '2', 'd', 30),
       ('region3', '2', 'a', 40);
5 rows affected

CREATE TABLE table_2 (
    region VARCHAR(255),
    period VARCHAR(255),
    categories VARCHAR(255),
    other_value INT
)

INSERT INTO table_2 (region, period, other_value)
VALUES ('region1', '1', 4),
       ('region2', '1', 5),
       ('region3', '2', 6);
3 rows affected

select t1.*,
    case when row_number() over(partition by t1.region, t1.period order by t1.category) = 1
        then t2.other_value
    end as other_value
from table_1 t1
left join table_2 t2 on t2.region = t1.region and t2.period = t1.period
order by t1.region, t1.period, t1.category


region
period
category
some_value
other_value


region1
1
a
20
4

region1
1
b
10
null

region2
1
c
30
5

region2
2
d
30
null

region3
2
a
40
6

region	period	category	some_value	other_value
region1	1	a	20	4
region1	1	b	10	null
region2	1	c	30	5
region2	2	d	30	null
region3	2	a	40	6