title: Splitting rows in SQL
body: <p>Can someone please let me know how to get the different segments of the three rows that are intersecting in different ways using SQL? The three rows in #t2 represent sets A,B, C - I am looking for A I B, A I C, B I C, A I B I C, A' , B', C' etc., (7 possible segments with 3 rows as in a Venn diagram) where I is the Intersection.</p>
  
  <p>I am looking for a generic solution which can handle n number of rows in #t2.</p>
  
  <pre><code>-- SQL Code Begin
  create table #t1 (key1 int, key2 int) -- for each Key1 there can be 1 or more Key2
  go
  create table #t2 (row_id int identity(101, 1), key1 int) --row_id is the primary key
  go 
  
  insert into #t1
  select 1, 11 union select 1, 12 union select 1, 13 union select 1, 14 union 
  select 2, 13 union select 2, 15 union select 2, 16 union select 2, 17 union 
  select 3, 13 union select 3, 12 union select 3, 16 union select 3, 17
  
  -- 1 --&gt; 11, 12, 13, 14
  -- 2 --&gt; 13, 15, 16, 17
  -- 3 --&gt; 13, 12, 16, 17
  
  insert into #t2 (key1)
  select 1 union select 2 union select 3
  
  -- SQL Code End
  </code></pre>
  
  <p>The output I am looking for is,</p>
  
  <pre><code>1001  11 (A')
  1001  14 (A')
  1002  12 (A I C - A I B I C)
  1003  13 (A I B I C)
  1004  15 (B')
  1005  16 (B I C - A I B I C)
  1005  17 (B I C - A I B I C)
  </code></pre>
  
  <p>The output has 5 segments, instead of the possible 7 as two of them are NULL.</p>