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

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

CodePudding user response：

Assuming not available means null, you can do:

select
  x.id,
  group_concat(distinct t) as types
from (select id from t) x
left join (
  select id, type1 as t from t
  union all select id, type2 from t
  union all select id, type3 from t
  union all select id, type4 from t
  union all select id, type5 from t
) y on y.id = x.id
group by x.id
order by x.id

Result:

 id  types 
 --- ----- 
 1   a,b   
 2   a,c   
 3

See example at db<>fiddle.

CodePudding user response：

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