SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) 
 Jul 25 2020 11:26:55 
 Copyright (C) 2019 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create TABLE PRODUCTDeatils 
(
    Product varchar(50),
    Date Datetime
)

insert into PRODUCTDeatils
select 'Ball', '2020-05-14 12:16:07.053' union
select 'Bat', '2020-05-14 12:17:07.053' union
select 'Gloves,Bat,Pad', '2020-05-15 01:17:07.053' union
select 'Helmet', '2020-05-16 02:16:07.053' union
select 'Ankle', '2020-05-18 02:16:07.053' union
select 'Stump', '2020-05-14 02:16:07.053'

select * from PRODUCTDeatils order by date


Product
Date


Stump
2020-05-14 02:16:07.053

Ball
2020-05-14 12:16:07.053

Bat
2020-05-14 12:17:07.053

Gloves,Bat,Pad
2020-05-15 01:17:07.053

Helmet
2020-05-16 02:16:07.053

Ankle
2020-05-18 02:16:07.053


SELECT DISTINCT
    LTRIM(RTRIM(value)) AS Product, date, ROW_NUMBER() over (partition by trim(value) order by date) rn
INTO #tmp3
FROM PRODUCTDeatils
CROSS APPLY STRING_SPLIT(LTRIM(RTRIM(Product)), ',')    

SELECT  
     STUFF((SELECT '; ' + Product
            FROM 
                (SELECT DISTINCT TOP 100
                     Product,date
                 FROM #tmp3 where rn = 1) x  
            ORDER BY date
            FOR XML PATH('')), 1, 2, '') Text

drop table if exists #temp
drop table if exists #tmp3


Text


Stump; Ball; Bat; Gloves; Pad; Helmet; Ankle

Product	Date
Stump	2020-05-14 02:16:07.053
Ball	2020-05-14 12:16:07.053
Bat	2020-05-14 12:17:07.053
Gloves,Bat,Pad	2020-05-15 01:17:07.053
Helmet	2020-05-16 02:16:07.053
Ankle	2020-05-18 02:16:07.053