SQL Server 2019

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

select @@version;

create table table1(t1_key varchar(20), value varchar(30));

create table table2(t2_key varchar(20), value varchar(30));

insert into table1 values('key1','word1')
insert into table1 values('key1','word2')
insert into table1 values('key1','word3')
insert into table1 values('key2','word4')
insert into table1 values('key2','word5')
insert into table1 values('key3','word6')

insert into table2 values('otherkey1',	'word1')
insert into table2 values('otherkey2',	'word1')
insert into table2 values('otherkey2',	'word2')
insert into table2 values('otherkey2',	'word3')
insert into table2 values('otherkey2',	'word9')
insert into table2 values('otherkey3',	'word4')
insert into table2 values('otherkey3',	'word5')
insert into table2 values('otherkey4',	'word4')
insert into table2 values('otherkey5',	'word1')
insert into table2 values('otherkey6',	'word6')

select t1_key,t2_key,combined,count(t1_key) from (

select aggr.t1_key ,aggr.t2_key ,(select STRING_AGG(value,' ') from table2 where t2_key = aggr.t2_key ) as combined from 
(select t1_key,t2_key from table1 left join table2 on table1.value = table2.value ) as aggr ) as whole

group by t1_key,t2_key,combined

(No column name)
Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) Jul 25 2020 11:26:55 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

t1_key	t2_key	combined	(No column name)
key1	otherkey1	word1	1
key1	otherkey2	word1 word2 word3 word9	3
key1	otherkey5	word1	1
key2	otherkey3	word4 word5	2
key2	otherkey4	word4	1
key3	otherkey6	word6	1