0

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

Ravi
  • 1,157
  • 1
  • 9
  • 19
priya
  • 1
  • 2
  • 3
    Hello priya, I would like to help but I am not sure if I understand your question correctly. Would it be possible to elaborate more, or give a simple example with an expected outcome ? It would also be helpful if the question could be formatted in a more readable way. – Ely Oct 28 '18 at 06:20
  • 1
    Yes, Sample data and required output including your try will be more useful – Prashant Pimpale Oct 28 '18 at 06:25
  • This is a Powerset, so https://stackoverflow.com/questions/36859361/powersetall-combinations-of-a-resultset-in-t-sql would solve it. – Corion Oct 28 '18 at 06:46
  • I am sorry if I didn't clarify well before, I need a include and exclude list as shown in the example above. thanks for your help! – priya Oct 28 '18 at 08:55

1 Answers1

1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786