SQL Server 2022

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

create table mytable (
  userId  varchar(20),
  discountCode  varchar(20),   
  FLAG int
);

insert into mytable values
('abo1',    'remise 1',        1),
('abo1',    'Remise 2',        2),
('abo1',    'remise 100%',     0),
('abo1',    'remise 16%',      0),
('abo2',    'remise 1',        0),
('abo2',    'Remise 2',        0),
('abo2',    'remise 100%',     0),
('abo2',    'remise 16%',      0);
8 rows affected

SELECT *
FROM (
  SELECT userId, 
         discountCode, 
         FLAG, 
         SUM(FLAG) OVER (PARTITION BY userId ) AS SM
  FROM mytable
) as s
-- WHERE FLAG <> 0 OR SM = 0


userId
discountCode
FLAG
SM


abo1
remise 1
1
3

abo1
Remise 2
2
3

abo1
remise 100%
0
3

abo1
remise 16%
0
3

abo2
remise 1
0
0

abo2
Remise 2
0
0

abo2
remise 100%
0
0

abo2
remise 16%
0
0


SELECT userId, STRING_AGG(discountCode, '|') AS discounts
FROM (
  SELECT userId, 
         discountCode, 
         FLAG, 
         SUM(FLAG) OVER (PARTITION BY userId ) AS SM
  FROM mytable
) as s
WHERE FLAG <> 0 OR SM = 0
GROUP BY userId


userId
discounts


abo1
remise 1|Remise 2

abo2
remise 1|Remise 2|remise 100%|remise 16%

userId	discountCode	FLAG	SM
abo1	remise 1	1	3
abo1	Remise 2	2	3
abo1	remise 100%	0	3
abo1	remise 16%	0	3
abo2	remise 1	0	0
abo2	Remise 2	0	0
abo2	remise 100%	0	0
abo2	remise 16%	0	0

userId	discounts
abo1	remise 1\|Remise 2
abo2	remise 1\|Remise 2\|remise 100%\|remise 16%