declare @t table 
(
  segment int, 
  subsegment int, 
  primary key (segment,subsegment)
)
insert @t 
select 1,33 union all 
select 1,22 union all
select 2,33 union all
select 2,22 union all
select 3,33 union all
select 3,22 union all
select 3,44

segment   partner
-------   -------
1         2

segment   partner
-------   -------
1         2
2         1

quote:

---

Originally posted by jezemine

just broadening the audience a little.

---

;WITH Yak(Segment, SubSegment)
AS (
	SELECT		s.Segment,
			g.SubSegment
	FROM		(
				SELECT		Segment
				FROM		@t
				GROUP BY	Segment
			) AS s
	CROSS APPLY	(
				SELECT DISTINCT	',' + CAST(y.SubSegment AS VARCHAR(12))
				FROM		@t AS y
				WHERE		y.Segment = s.Segment
				ORDER BY	',' + CAST(y.SubSegment AS VARCHAR(12))
				FOR XML		PATH('')
			) AS g(Subsegment)
)

SELECT		y1.Segment,
		y2.Segment AS Partner
FROM		Yak AS y1
INNER JOIN	Yak AS y2 ON y2.SubSegment = y1.SubSegment
WHERE		y1.Segment < y2.Segment

SELECT		t1.Segment,
		t2.Segment AS [Partner]
FROM		@t AS t1
INNER JOIN	@t AS t2 ON t2.SubSegment = t1.SubSegment
INNER JOIN	(
			SELECT		Segment,
					COUNT(*) AS Items
			FROM		@t
			GROUP BY	Segment
		) AS x ON x.Segment IN (t1.Segment, t2.Segment)
WHERE		t1.Segment < t2.Segment
GROUP BY	t1.Segment,
		t2.Segment
HAVING		MIN(x.Items) = MAX(x.Items)

SELECT MIN(t.segment),MAX(t.segment),LEFT(b.list,LEN(b.list)-1) as sslist 
FROM (select distinct segment from @t) t
CROSS APPLY(SELECT cast(subsegment as varchar(5)) + ',' as [text()]
            FROM @t
            WHERE segment=t.segment
            FOR XML PATH(''))b(list)
GROUP BY LEFT(b.list,LEN(b.list)-1)
HAVING MAX(t.segment)<>MIN(t.segment)

;
With CTE (segment,list) AS
(
SELECT t.segment,LEFT(b.list,LEN(b.list)-1) as sslist 
FROM (select distinct segment from @t) t
CROSS APPLY(SELECT cast(subsegment as varchar(5)) + ',' as [text()]
            FROM @t
            WHERE segment=t.segment
            ORDER BY subsegment
            FOR XML PATH(''))b(list)
)

SELECT * FROM CTE c1
INNER JOIN CTE c2
ON c2.list=c1.list
and c2.segment <> c1.segment

quote:

---

Originally posted by jezemine

you guys are great.

I'd use this for an interview question, but I think it would take the whole hour.

Plus, it's not fair to use it if I couldn't do it myself, right?

---

elsasoft.org

---

declare @t2 table
(
  s int,
  l varchar(max),
  primary key (s)
)

;with yak(
    s,
    ls,
    l)
as(
    select
        segment as 's',
        min(subsegment) as 'ls',
        '.' + convert(varchar(max), min(subsegment)) as 'l'
    from
        @t
    group by
        segment
    union all
    select
        t.segment,
        t.subsegment as 'ls',
        y.l+'.'+convert(varchar(max),t.subsegment) as 'l'
    from
        @t t
        inner join yak y
            on t.segment = y.s and
               t.subsegment > y.ls and
               not exists (
                    select 1
                    from
                        @t t1
                    where
                        t.segment = t1.segment and
                        t1.subsegment > y.ls and
                        t.subsegment > t1.subsegment)
)
insert into @t2
select
    s,
    max(l)
from
    yak
group by
    s

select
    t1.s as 'segment',
    t2.s as 'partner'
from
    @t2 t1
    inner join @t2 t2
        on t1.l = t2.l and
           t1.s <> t2.s

SELECT A.segment, B.segment
FROM @t AS A
INNER JOIN @t AS B ON A.segment <> B.segment
GROUP BY A.segment, B.segment
HAVING POWER(COUNT(CASE WHEN A.subsegment = B.subsegment THEN 1 END),2) = COUNT(*)

quote:

---

Originally posted by Arnold Fribble

Short and sweet:

SELECT A.segment, B.segment
FROM @t AS A
INNER JOIN @t AS B ON A.segment <> B.segment
GROUP BY A.segment, B.segment
HAVING POWER(COUNT(CASE WHEN A.subsegment = B.subsegment THEN 1 END),2) = COUNT(*)

(and also very, very slow!)

---

create table #t
(
  segment int, 
  subsegment int, 
  primary key (segment,subsegment)
)
insert #t 
select 6,1 union all 
select 5,1 union all
select 4,33 union all 
select 4,22 union all 
select 1,33 union all 
select 1,22 union all
select 2,33 union all
select 2,22 union all
select 3,33 union all
select 3,22 union all
select 3,44

declare @sql varchar(max)

set	@sql = ''

select	@sql = @sql + ',' + quotename(subsegment)
from	(
		select		subsegment
		from		#t
		group by	subsegment
	) AS q

set @sql = stuff(@sql, 1, 1, '')

set @sql = '
-- peso 2
select	segment,
	partnergroup
from	(
		select	segment,
			partnergroup,
			count(*) over (partition by partnergroup) AS t
		from	(
				SELECT	p.segment,
					rank() over (order by ' + @sql + ') as partnergroup
				FROM	#t
				PIVOT	(
						MAX(subsegment)
						for subsegment in (' + @sql + ')
					) AS p
			) as r
	) AS y
where	t > 1'

exec(@sql)

drop table #t

declare @t table 
(
  segment int, 
  subsegment int, 
  primary key (segment,subsegment)
)
insert @t 
select 1,33 union all 
select 1,22 union all
select 2,33 union all
select 2,22 union all
select 3,33 union all
select 3,22 union all
select 3,44 union all
select 4,33 union all 
select 4,22 union all 
select 5,30 union all 
select 5,25 union all 
select 6,33 union all 
select 6,22 

declare @t1 table (segment int, prodSum int)
insert into @t1
select  segment, sum(RowNum * subsegment) as prodSum
from
(
	SELECT	ROW_NUMBER() OVER (PARTITION BY segment ORDER BY segment) as RowNum, 
			segment, subsegment
	FROM	@t
) t1
group by segment

select t2.segment 
from
	(
		select	prodSum
		from	@t1
		group by prodSum
		having COUNT(*) > 1
	) t1
	join @t1 t2 on t1.prodSum = t2.prodSum
order by t2.segment