SQL Server Concatenate three different columns into a Comma-Separated without repeated values

Question

The next table is a simplification of my problem in SQL Server:

ID  COLUMN_A    COLUMN_B    COLUMN_C
-------------------------------------
1    A            B           C
1    A            B           D
1    B            C           D

I want to get a group with the columns concatenated by comma without repeated values. I tried to use STRING_AGG() but it returns:

ID  COLUMN_A    COLUMN_B    COLUMN_C
-------------------------------------
1    A, A, B    B, B, C     C, D, D

This is the query I have done:

SELECT ID, STRING_AGG(COLUMN_A, ', ') AS COL_A, STRING_AGG(COLUMN_B, ', ') AS COL_B,
 STRING_AGG(COLUMN_C, ', ') AS COL_C   
FROM MYTABLE
GROUP BY ID;

I would like the next result:

ID  COLUMN_A    COLUMN_B    COLUMN_C
-------------------------------------
1    A, B        B, C        C, D

Thank you!

Answer 1

Without using window functions. The union might slow things down, but give it a try and see if you can tolerate the performance.

 with 
 cte1 (id, col, indicator) as 
 
  (select id, column_a, 'col1' from t union
   select id, column_b, 'col2' from t union
   select id, column_c, 'col3' from t),
  
 cte2 (id, indicator, agg) as
  
  (select id, indicator, string_agg(col,',') 
   from cte1
   group by id, indicator)
  
 select id,
        max(case when indicator='col1' then agg end) as column_a,
        max(case when indicator='col2' then agg end) as column_b,
        max(case when indicator='col3' then agg end) as column_c
 from cte2
 group by id;