case when on many lines resulting out of a group by in teradata

Question

i'm trying to make a concat of a column of a bunch of lines but with some conditions to verify before adding the value of the column, so basically let's say i have a table that contains a userId, a discountCode, and a flag (0,1,2)

userId  discountCode    FLAG 
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

what i need is that for each user i have to concatenate the discountCode in one column but on cases : if all the flags are 0 for that user we take all the discountCodes if there is a mixt of 0 and other values we do not add the discountCodes that have a flag 0 if all the discount code are <> 0 we add them all. so the result of the previous data should be :

userId  discountCode
abo1    remise 1 | Remise 2
abo2    remise 1 | Remise 2 |  remise 100% | remise 16%


 TRIM(
    TRAILING ',' FROM (
      XMLAGG(TRIM(COALESCE(discountCode , -1) || '') ) (VARCHAR(1000)) 
    )

i used this function to group the discountCode in one column but i dont know how to check for the conditions of the flags

Answer 1

We can use the SUM() window function to calculate the total for each user. then, we exclude rows where the flag is 0 and the total is greater than 0 by applying the condition WHERE FLAG <> 0 OR SM = 0 :

SELECT userId, TRIM(
                 TRAILING ',' FROM (
                   XMLAGG(TRIM(COALESCE(discountCode , -1) || '') ) (VARCHAR(1000)) 
               ) 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;

Demo on sql server