SQL Server 2019

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

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU1) (KB4527376) - 15.0.4003.23 (X64) 
	Dec  6 2019 14:53:33 
	Copyright (C) 2019 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table t(id int, sudid int, LanguageCode varchar(50), caption varchar(100))
insert into t values 
(1,0,'en-us','hello'),
(1,0,'fr-fr','bonjour'),
(2,1,'en-us','how are you?'),
(2,1,'fr-fr','comment allez-vous?'),
(3,0,'en-us','how is the weather?')
5 rows affected

select Id, SudId, STRING_AGG(LanguageCode + ': ' + caption, ', ') as Translations from t 
group by Id, SudId
Having count(*) = 2


Id
SudId
Translations


1
0
en-us: hello, fr-fr: bonjour

2
1
en-us: how are you?, fr-fr: comment allez-vous?


select Id, SudId, STRING_AGG(LanguageCode + ': ' + caption, ', ') as Translations from t 
group by Id, SudId
Having count(*) <> 2


Id
SudId
Translations


3
0
en-us: how is the weather?


select Id, SudId, STRING_AGG(LanguageCode + ': ' + caption, ', ') as Translations,
       CASE WHEN count(*) = 2 Then 1 else 0 end as TranslationComplete
from t 
group by Id, SudId


Id
SudId
Translations
TranslationComplete


1
0
en-us: hello, fr-fr: bonjour
1

3
0
en-us: how is the weather?
0

2
1
en-us: how are you?, fr-fr: comment allez-vous?
1

Id	SudId	Translations
1	0	en-us: hello, fr-fr: bonjour
2	1	en-us: how are you?, fr-fr: comment allez-vous?

Id	SudId	Translations	TranslationComplete
1	0	en-us: hello, fr-fr: bonjour	1
3	0	en-us: how is the weather?	0
2	1	en-us: how are you?, fr-fr: comment allez-vous?	1