Home > front end >  How to concat distinct values over multiple columns in MS SQL?
How to concat distinct values over multiple columns in MS SQL?

Time:05-20

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:

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

  • Related