0

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
Arun Palanisamy
  • 5,281
  • 6
  • 28
  • 53
Ankit
  • 1
  • 1
  • Seems similar to [this question](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) – Taavi Tiitsu Dec 29 '20 at 07:47
  • 2
    Please tag the DBMS that you are using along with the version. E.g., `Oracle 11g`, `MSSQL 2012` etc., – Arun Palanisamy Dec 29 '20 at 07:49

2 Answers2

0

In standard SQL, you can represent this as:

select id,
       listagg(text, '; ') within group (order by rank_order) as text
from t
group by id;

Although most databases support this functionality, the exact syntax depends on the database -- as does inserting a newline character (which might also depend on the operating system).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

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

Ankit
  • 1
  • 1