Merge values in a column (B) based on common values in column (A) in SQL table

Question: Using SQL, how would you Merge values in a column (B) based on common values in column (A)?

Table Structure: I have a SQL table (shown below), where Column A has ID’s and Column B contains Text related to ID’s and Column C contains Rank Order (the order in which text should be should be sorted).

ID	TEXT	RANK_ORDER
ABC001	ID: ABC001 – NEAREST LANDMARK – SHOPPING CENTRE	-999
ABC001	TRAVEL 80 M NORTH FROM SC	-900
ABC001	THROUGH PEDESTRIAN CROSSING	10.1
ABC002	ID: ABC002 – NEAREST LANDMARK – PUBLIC TOILET	-999
ABC002	TRAVEL 150 M NORTH FROM SC	-900
ABC002	THROUGH PARK ACCESS RD	10.1
ABC003	ID: ABC003 – NEAREST LANDMARK – REHABILITATION CENTRE	-999
ABC003	TRAVEL 1300M WEST FROM RC	-900
ABC003	THROUGH UNMADE RD	10.1
ABC003	LOCKED GATES	10.5
ABC003	CALL RC FOR ACCESS	20.1

Expected End Result: The resultant table should look like the table shown below:

ID	TEXT
ABC001	ID: ABC001 – NEAREST LANDMARK – SHOPPING CENTRE TRAVEL 80 M NORTH FROM SC THROUGH PEDESTRIAN CROSSING
ABC002	ID: ABC002 – NEAREST LANDMARK – PUBLIC TOILET TRAVEL 150 M NORTH FROM SC THROUGH PARK ACCESS RD
ABC003	ID: ABC003 – NEAREST LANDMARK – REHABILITATION CENTRE TRAVEL 1300M WEST FROM RC THROUGH UNMADE RD LOCKED GATES CALL RC FOR ACCESS

Answer

Thanks Gordon, LISTAGG Works on SQLServer 2016 onwards, where I am on SQLServer 2012. But I found STRING_AGG works well. The following query is the solution.

    select ID, STRING_AGG(TEXT, CHAR(13)) within group (Order by RANK_ORDER) as TEXT
    from t
    GROUP BY ID

I am unable to get Carriage Return in results, it looks like SSMS issue.

Cheers, Ankit

Advertisement

Answer