Sql-server – Combination Identifier

sql servert-sql

I want to identify in one column those who are present in the data but there is limit to only 10 nests. Is there easier or some other way for me to get the result that I want?

Code

I hope you could help me.

Best Answer

You can do that using a CASE expression:

select a,b,c,d,
       case (a + b * 2 + c * 4 + d * 8)
          when 0 then 'none'
          when 1 then 'a'
          when 2 then 'b'
          when 4 then 'c'
          when 8 then 'd'
          when 3 then 'a-b'
          when 7 then 'a-b-c'
          when 5 then 'a-c'
          when 9 then 'a-d'
          when 6 then 'b-c'
          when 10 then 'b-d'
          when 14 then 'b-c-d'
          when 12 then 'c-d'
          when 15 then 'a-b-c-d'
          else 'error'
        end as present
from the_table;

This creates a "bitset" with one bit for each column, then tests for the values that uniquely identify each combination.

This assumes that there are no NULL values in those columns.

Online example on db<>fiddle

Another option might be to concat expressions:

select concat(case when a = 1 then 'a-' end, 
              case when b = 1 then 'b-' end, 
              case when c = 1 then 'c-' end, 
              case when d = 1 then 'd-' end)
from the_table;

That leaves an ugly dangling - at the end, but you could probably remove that with a combination of substring() and/or replace() calls.

Related Question