Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Grouping question

Author  Topic 

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2013-05-10 : 12:22:47
I have the following simple data set:

with SCC_CTE  (Retained_ID, Dropped_ID)
AS (Select 15799 , 8388 union all
select 52941, 8388 union all
select 52941, 15799 union all
select 80833, 8388 union all
select 80833 , 15799)
select *
from SCC_CTE


I would like to procude the following output:



;with Result_CTE (Retained_ID, Dropped_ID)
AS (Select 80833 , 8388 union all
select 80833, 15799 union all
select 80833, 52941)
select * from Result_CTE


Any suggestions please?

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-10 : 12:51:38
Something like this:

[CODE]


with SCC_CTE (Retained_ID, Dropped_ID)
AS (Select 15799 , 8388 union all
select 52941, 8388 union all
select 52941, 15799 union all
select 80833, 8388 union all
select 80833 , 15799)
SELECT DISTINCT a.Retained_ID, b.Dropped_ID from SCC_CTE a cross join
(SELECT DISTINCT Retained_ID as Dropped_ID from SCC_CTE
UNION
SELECT DISTINCT Dropped_ID as Dropped_ID from SCC_CTE) b where a.Retained_ID = 80833 and b.Dropped_id <> 80833;


[/CODE]
Go to Top of Page
   

- Advertisement -