SQL: 2 Zeilen zu einer zusammenfassen

SELECT name, max(berufswunsch1), max(berufswunsch2) FROM tabellenname GROUP BY by name

SELECT name
    ,   case when bw1 is null then '' else bw1 ||', ' end  
     || case when bw2 is null then '' else bw2       end   
      as bw 
  FROM  (   
 SELECT name, max(berufswunsch1) as bw1, max(berufswunsch2) as bw2 
  FROM tabellenname GROUP BY by name 
) x
;

SELECT name,   
CASE WHEN (bw1 IS not null and bw2 is not null) THEN (bw1 + ', '+ bw2)   
         ELSE (COALESCE(  bw1 , bw2 ) END AS 'Berufswunsch'    
FROM  (   
 SELECT name, max(berufswunsch1) as bw1, max(berufswunsch2) as bw2 
  FROM BewProf GROUP BY name
) x
;

Select Name
     , Max(ErsterWertvontPpbBkz_baus) as ErsterWertvontPpbBkz_baus
     , Max( ErsterWertvontPpbBkz_aarb) as ErsterWertvontPpbBkz_aarb 
    FROM (  
SELECT Name, 
   MIN(tPersBewProfBkz.tPpbBkz) AS ErsterWertvontPpbBkz_baus , '' as ErsterWertvontPpbBkz_aarb  
FROM tPersBewProf 
 INNER JOIN tPersBewProfBkz ON tPersBewProf.tBpId = tPersBewProfBkz.tPpbBpId 
 WHERE (((tPersBewProf.tBpStellenTyp)='baus') AND ((tPersBewProf.tBpAktAb)<=Convert(date,GetDate())   
 AND ((tPersBewProf.tBpAktBis)>=Convert(date,GetDate()) Or (tPersBewProf.tBpAktBis) Is Null) 
GROUP BY Name
UNION ALL
SELECT Name, '' as ErsterWertvontPpbBkz_baus , MIN(tPersBewProfBkz.tPpbBkz) AS ErsterWertvontPpbBkz_aarb   
FROM tPersBewProf 
 INNER JOIN tPersBewProfBkz ON tPersBewProf.tBpId = tPersBewProfBkz.tPpbBpId 
 WHERE (((tPersBewProf.tBpStellenTyp)='aarb') AND ((tPersBewProf.tBpAktAb)<=Convert(date,GetDate())   
 AND ((tPersBewProf.tBpAktBis)>=Convert(date,GetDate()) Or (tPersBewProf.tBpAktBis) Is Null) 
AND ((tPersBewProfBkz.tPpbPrio)=-1) AND ((tPersBewProfBkz.tPpbBkz)<>'99999999')))  
GROUP BY Name
) x
GROUP BY Name
;