By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test
(
id int,
callID int,
callDateTime datetime2,
history varchar(max)
);
insert into test values(1,1,'2021-01-01 10:00:00', 'Amount: 10, Ref:123, ID:123');
insert into test values(2,1,'2021-01-01 10:01:00', 'Amount: 10, Ref:123, ID:123, ID:123');
insert into test values(3,2,'2021-01-01 11:00:00', 'Amount:12.44, Ref:SIS, ID:124');
insert into test values(4,2,'2021-01-01 11:02:00', 'Amount:11.22, Ref:Dad, ID:124');
insert into test values(5,2,'2021-01-01 11:01:00', 'Amount:11.22, Ref:Mum, ID:124');
insert into test values(6,3,'2021-01-01 12:00:00', 'Amount:11, ID:125');
6 rows affected
select * from test
| id | callID | callDateTime | history |
|---|---|---|---|
| 1 | 1 | 2021-01-01 10:00:00.0000000 | Amount: 10, Ref:123, ID:123 |
| 2 | 1 | 2021-01-01 10:01:00.0000000 | Amount: 10, Ref:123, ID:123, ID:123 |
| 3 | 2 | 2021-01-01 11:00:00.0000000 | Amount:12.44, Ref:SIS, ID:124 |
| 4 | 2 | 2021-01-01 11:02:00.0000000 | Amount:11.22, Ref:Dad, ID:124 |
| 5 | 2 | 2021-01-01 11:01:00.0000000 | Amount:11.22, Ref:Mum, ID:124 |
| 6 | 3 | 2021-01-01 12:00:00.0000000 | Amount:11, ID:125 |
select callID, historyEdit
from
(
select distinct top 100000 callID, callDateTime,
substring(history, 0, charindex(', ID:',history)) historyEdit
from test
order by callDateTime
)t
| callID | historyEdit |
|---|---|
| 1 | Amount: 10, Ref:123 |
| 1 | Amount: 10, Ref:123 |
| 2 | Amount:12.44, Ref:SIS |
| 2 | Amount:11.22, Ref:Mum |
| 2 | Amount:11.22, Ref:Dad |
| 3 | Amount:11 |
select CallID, Concat([1],',', [2],',',[3]) concatenated_value_in_order
from
(
select CallID, historyEdit, ROW_NUMBER() over (partition by callID order by callID) as rowNum
from
(
select callID, historyEdit
from
(
select distinct top 100000 callID, callDateTime,
substring(history, 0, charindex(', ID:',history)) historyEdit
from test
order by callDateTime
)t
) a
)b
PIVOT(max(historyEdit) for rowNum IN ([1],[2],[3])) piv
| CallID | concatenated_value_in_order |
|---|---|
| 1 | Amount: 10, Ref:123,Amount: 10, Ref:123, |
| 2 | Amount:12.44, Ref:SIS,Amount:11.22, Ref:Mum,Amount:11.22, Ref:Dad |
| 3 | Amount:11,, |