How to concat distinct values over multiple columns in MS SQL?

Question

I have a table like this

id	type 1	type 2	type 3	type 4	type 5
1	a	b	b	not available	not available
2	c	c	a	not available	not available
3	not available	not available	not available	not available	not available

What query will help me get a concat on distinct values in these columns and also rid of the "not available", ie:

id	types
1	a, b
2	c, a
3

Answer 1

You can use CROSS APPLY

select
  id,
  string_agg(s.tp, ',') as types
from t
cross apply (
  select type1 union 
  select type2 union
  select type3 union
  select type4 union
  select type5  
) s(tp)
group by id
order by id;

db<>fiddle