How to combine multiple columns into one single row and aggregate the strings into list or one string

Question

How to combine multiple columns into one single row . eg if you have two columns in a table called name, surname, and location.

neo hlophi london

peter lock asia

bernad jules asia

result :

I want to have name and surname combined by a dash e.g : neo|hlophi in a new column called combination.

location combination

london neo|hlophi

asia peter|lock,bernad|jules

Answer 1

Try (SQL Server 2017 and up):

DECLARE @t TABLE (name varchar(30), surname VARCHAR(30), location VARCHAR(30)) 
INSERT INTO @t (name, surname, location) 
VALUES 	('neo', 'hlophi', 'london'), 	('peter', 'lock', 'asia'), 	('bernad', 'jules', 'asia') 


SELECT location, STRING_AGG(CONCAT(name, '|', surname),',') FROM @t GROUP BY location

Answer 2

SELECT concat(localtion, ' ', name, '|', surename)
FROM  tbl