Generate a column based on other column within each case

Question

input:

| case_no | activity | country |
|---------|----------|---------|
|       1 | a1       | A       |
|       1 | a1       | A       |
|       1 | a1       | B       |
|       2 | a2       | C       |
|       2 | a2       | D       |
|       3 | a3       | E       |
|       3 | a3       | E       |

output a new column case_countries:

| case_no | activity | country | case_countries |
|---------|----------|---------|----------------|
|       1 | a1       | A       | A,B            |
|       1 | a1       | A       | A,B            |
|       1 | a1       | B       | A,B            |
|       2 | a2       | C       | C,D            |
|       2 | a2       | D       | C,D            |
|       3 | a3       | E       | E              |
|       3 | a3       | E       | E              |

I want to generate column case_countries keeps all countries of each activities within a case. any suggestions?

ps. i use Teradata

Answer 1

It's not really ugly in Teradata as Tim suggested, but still a more complicated:

SELECT
    t1.case_no,
    t1.activity,
    t1.country,
    t2.case_countries
FROM yourTable  t1
INNER JOIN
 (
   SELECT case_no, activity,
      Trim(Trailing ',' FROM XmlAgg(country||',' ORDER BY country) (VARCHAR(200))) AS case_countries
   FROM 
    (
      SELECT DISTINCT case_no, activity, country
      FROM yourTable
    ) t2
   GROUP BY 1,2 
 ) t2
ON t1.case_no = t2.case_no
AND t1.activity = t2.activity;