SQL Server 2022

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


(No column name)


Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 
	Oct  8 2022 05:58:25 
	Copyright (C) 2022 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE my_table_name
(
    id         INT,
    LS_NoSerie VARCHAR(255),
    DO_Tiers   VARCHAR(255),
    rn         INT,
    totalMatch INT
);

INSERT INTO my_table_name (id, LS_NoSerie, DO_Tiers, rn, totalMatch)
VALUES (1, N'A', N'I607', 1, 80);

INSERT INTO my_table_name (id, LS_NoSerie, DO_Tiers, rn, totalMatch)
VALUES (2, N'A', N'I3446', 2, 80);

INSERT INTO my_table_name (id, LS_NoSerie, DO_Tiers, rn, totalMatch)
VALUES (3, N'A', N'I3446', 3, 80);

INSERT INTO my_table_name (id, LS_NoSerie, DO_Tiers, rn, totalMatch)
VALUES (4, N'B', N'I3446', 1, 60);

INSERT INTO my_table_name (id, LS_NoSerie, DO_Tiers, rn, totalMatch)
VALUES (5, N'B', N'A4123', 2, 60);

INSERT INTO my_table_name (id, LS_NoSerie, DO_Tiers, rn, totalMatch)
VALUES (6, N'B', N'B7856', 3, 60);
6 rows affected

SELECT 
  MAX(CASE WHEN rn = 1 THEN id END) AS id, 
  ls_noserie,
  STRING_AGG(CONCAT(do_tiers, ''), ',') AS tiers,
  1 AS rn, 
  MAX(CASE WHEN rn = 1 THEN totalmatch END) AS totalmatch
FROM my_table_name
GROUP BY ls_noserie
ORDER BY ls_noserie;


id
ls_noserie
tiers
rn
totalmatch


1
A
I607,I3446,I3446
1
80

4
B
I3446,A4123,B7856
1
60


Warning: Null value is eliminated by an aggregate or other SET operation.