MS SQL Server - SQL Query zum vereinigen von Datensätzen

Select 
 a.Col1
 , a.Col2
 , Case
  when b.Col3 IS NOT NULL Then b.Col3
  Else ''  
 End as Col3
 , b.Col4
From
 Table1 as a
Left Join Table2 as b on a.Col1 = b.Col1 and a.Col2 = b.Col2

WITH t AS (
	SELECT	ROW_NUMBER() OVER (PARTITION BY ID_Firma,ID_Person,Typ ORDER BY Kontakt) AS Zeile,
			ID_Firma,
			ID_Person,
			Typ,
			Kontakt
	FROM	tabelle
	WHERE	Typ IN ( 1,2 )
	), stamm AS (
	SELECT	DISTINCT
			ID_Firma,
			ID_Person,
			Firma,
			Name
	FROM	tabelle
WHERE	Typ IN ( 1,2 )
	)
SELECT	stamm.Firma,
		stamm.Name,
		t1.Kontakt AS Telefon1,
		t2.Kontakt AS Telefon2,
		t3.Kontakt AS Email
FROM	stamm
LEFT JOIN t t1
ON		stamm.ID_Firma = t1.ID_Firma
AND		stamm.ID_Person = t1.ID_Person
AND		t1.Typ = 1
AND		t1.Zeile = 1
LEFT JOIN t t2
ON		stamm.ID_Firma = t2.ID_Firma
AND		stamm.ID_Person = t2.ID_Person
AND		t2.Typ = 1
AND		t2.Zeile = 2
LEFT JOIN t t3
ON		stamm.ID_Firma = t3.ID_Firma
AND		stamm.ID_Person = t3.ID_Person
AND		t3.Typ = 2
AND		t3.Zeile = 1