I have data like below:
Employer_ID Gender First_Name Last_Name Keywords
----------- ------ ---------- ---------- ---------
101 M Ian SMITH Environment
101 M Ian SMITH Global warmimg
101 M Ian SMITH Earth
101 M Ian SMITH Air
101 M Ian SMITH Sound pollution
102 M Scott Tiger Heart attack
102 M Scott Tiger Medical
102 M Scott Tiger Heart surgery
I would like to have output as below. Group by Employer_Id, Gender, First_Name and Last_Name. All relevant Keywords should be merged to produce one row per Employer_Id, Gender, First_Name and Last_Name:-
Employer_ID Gender First_Name Last_Name Keywords
----------- ------ ---------- --------- ---------
101 M Ian SMITH Environment Global warmimg Earth Air Sound pollution
102 M Scott Tiger Heart attack Medical Heart surgery
You can achieve it using String AGG as suggested by @Zhorov,
SELECT Employer_ID,Gender,First_Name,Last_Name,STRING_AGG(Keywords ,' ') AS Keywords
FROM #Temp
GROUP BY Employer_ID,Gender,First_Name,Last_Name