CODEWITHSUNDEEP
sqlsql-server
priya
priya

Reputation: 1

Finding a Combination of values with inclusion list

Given a column of values ('Black', 'White', 'Orange') with sequence numbers 1,2,3 respectively, i need to find the permutations and combinations such as below.

Example

Colortable Color Sequence

Black 1

White 2

Orange 3 ColorCombi table with 2 columns In NotIn

Black White, Orange

White Black, Orange

Orange Black, White

Black, White Orange

Black, Orange White

Orange, White Black

Black, White, Orange Null

Upvotes: 0

Views: 122

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

This is tricky to do. You can do it with a recursive CTE:

with t as (
      select v.*
      from (values ('Black', 1), ('White', 2), ('Orange', 3)) v(color, seq)
     ),
     combos as (
      select cast('' as varchar(max)) as ins, cast('' as varchar(max)) as outs, 0 as seq
      union all
      select c.ins + v.ins, c.outs + v.outs, c.seq + 1
      from t cross apply
           (values (',' + t.color, ''), ('', ',' + t.color)) as v(ins, outs) join
           combos c
           on t.seq = c.seq + 1
     )
select top (1) with ties stuff(ins, 1, 1, '') as ins, stuff(outs, 1, 1, '') as outs
from combos
order by seq desc ;

Here is a db<>fiddle.

Upvotes: 1

Related Questions