DECLARE @t1 TABLE(id varchar(10), [key] varchar(10), val ...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

DECLARE @t1 TABLE(id varchar(10), [key] varchar(10), val varchar(10))
DECLARE @t2 TABLE(id varchar(10), [key] varchar(10), val varchar(10))

SET NOCOUNT ON

INSERT @t1 VALUES('fred','hair','red') 
INSERT @t1 VALUES('fred','eyes','green') 
INSERT @t1 VALUES('fred','shoe','42' )
INSERT @t1 VALUES('joe','hair','red'  )
INSERT @t1 VALUES('joe','eyes','green' )
INSERT @t1 VALUES('joe','eyes','green' )
INSERT @t1 VALUES('greg','eyes','blue' )
INSERT @t1 VALUES('greg','hair','brown' )

INSERT @t2 VALUES('bob','hair','red') 
INSERT @t2 VALUES('bob','eyes','green') 
INSERT @t2 VALUES('bob','eyes','green') 

;WITH t1 AS ( 
SELECT t1.id, t1.[key], t1.val, count(*) count1, sum(count(*)) OVER(PARTITION BY t1.id) sum1 FROM @t1 t1 
group by t1.id, t1.[key], t1.val
), t2 as (
SELECT t2.id, t2.[key], t2.val, count(*) count1, sum(count(*)) OVER(PARTITION BY t2.id) sum1 FROM @t2 t2 
GROUP BY t2.id, t2.[key], t2.val
), t3 AS ( 
SELECT t1.*, sum(t1.count1) OVER(PARTITION BY t1.id) sum2
FROM t1 
JOIN t2 on t1.val = t2.val AND t1.[key]=t2.[key]
AND t1.count1 = t2.count1 AND t1.sum1 = t2.sum1
)
SELECT t3.id, t3.[key], t3.val FROM t3
JOIN @t2 t ON t3.[key] = t.[key] AND t3.val = t.val
WHERE t3.sum2 = t3.sum1

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached