Eliminate Duplicate data on a Join statement

[bmacbmac]

Hi. I know this has been asked a million times, but I can't seem to find an answer.

I have a table with the following data:

create table #temp (irecordid int, partyone varchar(20), partytwo varchar(20))
insert into #temp values (1, 'SMITH', NULL)
insert into #temp values (1, 'JACKSON', NULL)
insert into #temp values (2, 'ANDERSON', NULL)
insert into #temp values (3, 'JONES', NULL)
insert into #temp values (3, 'FARLEY', NULL)
insert into #temp values (3, 'OKERSON', NULL)
insert into #temp values (3, 'SHOLE', NULL)
insert into #temp values (1, NULL, 'BANK ONE')
insert into #temp values (2, NULL, 'CITIBANK')
insert into #temp values (2, NULL, 'WELLS FARGO')
insert into #temp values (3, NULL, 'MOUNTAIN WEST BANK')

I am trying to get a list of each distinct partyone and partytwo for each irecordid. Something like this:

Irecordid, party1, party2
1, SMITH, BANK ONE
1, JACKSON, NULL
2, ANDERSON, CITIBANK
2, NULL, WELLS FARGO
3, JONES, MOUNTAIN WEST BANK
3, FARLEY, NULL
3, OKERSON, NULL
3, SHOLE, NULL

I have tried this, but everything duplicates:

select distinct t1.irecordid,
t1.partyone,
t2.partytwo
from #temp t1
inner join #temp t2
on t1.irecordid = t2.irecordid
group by t1.irecordid, t1.partyone, t2.partytwo

I also tried this.. Which Almost gets me what I want.

select distinct t1.irecordid, t1.partyone, t1.partytwo
from #temp t1
outer apply
(select top 1 * from #temp t2
where t1.irecordid = t2.irecordid) t2
order by t1.irecordid, t1.partyone, t1.partytwo

Each name is on it's own row...
1, NULL, BANK ONE
1, JACKSON, NULL
1, SMITH, NULL

I'm hoping instead to get this instead:
1, JACKSON, BANK ONE
1, SMITH, NULL

Even if I have to insert into an additional #temp table and modify from there.

Any ideas?

Thanks!

 

[TamarGranor]

I don't understand how you get from your data to the result you want. Why doesn't it include:

1, 'JACKSON', 'BANK ONE'

for example. What's the connection?

Tamar

 

[bmacbmac]

There isn't necessarily a connection between Bank One and Jackson. Since there is only 1 party1 snd 2 party2's I'm hoping to just have two rows of results instead of three.

 

[djj55]

So, once a partytwo is used it does not get used again?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[TamarGranor]

Try using ROW_NUMBER with OVER to assign a unique number to each row in the group. Then match on those numbers. Something like this:

WITH csrPartyOne AS 
  (SELECT iRecordID, PartyOne, 
         ROW_NUMBER OVER (PARTITION BY iRecordID) AS RowNum 
     FROM (SELECT DISTINCT iRecordID, PartyOne 
             FROM #Temp) csrDistPartyOne), 

      csrPartyTwo AS 
  (SELECT iRecordID, PartyTwo, 
         ROW_NUMBER OVER (PARTITION BY iRecordID) AS RowNum 
     FROM (SELECT DISTINCT iRecordID, PartyTwo 
             FROM #Temp) csrDistPartyTwo)

   SELECT iRecordID, PartyOne, PartyTwo 
     FROM csrPartyOne
      FULL JOIN csrPartyTwo 
        ON csrPartyOne.iRecordID = csrPartyTwo.iRecordID
        AND csrPartyOne.RowNum = csrPartyTwo.RowNum

Tamar