Pivot Rows into column value-CodePudding

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

CodePudding user response：

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