CODEWITHSUNDEEP
sqlrowssplit
user26309
user26309

Reputation: 101

Splitting rows in SQL

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.

I am looking for a generic solution which can handle n number of rows in #t2.

-- 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 --> 11, 12, 13, 14
-- 2 --> 13, 15, 16, 17
-- 3 --> 13, 12, 16, 17

insert into #t2 (key1)
select 1 union select 2 union select 3

-- SQL Code End

The output I am looking for is,

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)

The output has 5 segments, instead of the possible 7 as two of them are NULL.

Upvotes: 1

Views: 835

Answers (3)

Dheer
Dheer

Reputation: 4066

If you can popluate the table 2 as 

1, Key1-Value 1
2, Key1-Value 2
4, Key1-Value 3

likewise if you have more key1's then the identity column as you have, should be populated as a binary tree. so that the values in t2 would be 

1 , 1
2 , 2
4 , 3

Then you need to do a select:

select sum(identity), key2
from t1, t2
where t1.key1 = t2.key1
groupby key2

the output you will get will be 

1   11
5   12
7   13
1   14
2   15
6   16
6   17

you can see that 1,2,4 will be A',B',C' 2 will be A|B , 7 will be A|B|C and likewise

Upvotes: 0

Tim C
Tim C

Reputation: 70608

If I have understood the problem correctly, I think you may have to resort to using a loop to cope with 'n' number of rows

DECLARE @Key2 INT
DECLARE @Subset VARCHAR(1000)
DECLARE @tblResults TABLE
(
    Key2 INT,
    Subset VARCHAR(1000)
)

SET @Subset = ''
SELECT @Key2 = MIN(Key2) FROM #t1

WHILE @Key2 IS NOT NULL
BEGIN
    SELECT @Subset = @Subset + CAST(Key1 AS VARCHAR(10))
    FROM #t1
    WHERE Key2 = @Key2

    INSERT INTO @tblResults (Key2, Subset)
    VALUES (@Key2, @Subset)

    SET @Subset = ''
    SELECT @Key2 = MIN(Key2) FROM #t1 WHERE Key2 > @Key2
END

SELECT * FROM @tblResults

Upvotes: 3

Amy B
Amy B

Reputation: 110091

How about this?

SELECT key2,
  CASE
  WHEN InA = 1 and InB = 1 and InC = 1 THEN 'ABC'
  WHEN InA = 0 and InB = 1 and InC = 1 THEN 'BC'
  WHEN InA = 1 and InB = 0 and InC = 1 THEN 'AC'
  WHEN InA = 1 and InB = 1 and InC = 0 THEN 'AB'
  WHEN InA = 1 and InB = 0 and InC = 0 THEN 'A'
  WHEN InA = 0 and InB = 1 and InC = 0 THEN 'B'
  WHEN InA = 0 and InB = 0 and InC = 1 THEN 'C'
  ELSE 'I''m broke'
  END as [SubSet]
FROM

(
SELECT key2,
  MAX(CASE WHEN key1 = 1 THEN 1 ELSE 0 END) as InA,
  MAX(CASE WHEN key1 = 2 THEN 1 ELSE 0 END) as InB,
  MAX(CASE WHEN key1 = 3 THEN 1 ELSE 0 END) as InC
FROM #t1
WHERE key1 in (1, 2, 3)
GROUP BY key2
) sub

ORDER BY key2

Upvotes: 0

Related Questions