Click here to Skip to main content
15,792,842 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
declare @tb1 table
( partner1 Varchar(5),
partner2 Varchar(5)

insert into @tb1
values( 'A','D'),

Select * from @tb1
   --output has to be like A D ,
   --                 B E ,
   --                 C F,

   --                 & not repeated like D A ,E B, F C

Query has to be generic if there are 3 columns then combination of 3 columns should not be repeated in any other row the example given is just for refernce
Updated 19-Apr-12 1:31am

I would have designed the db differently:

Give all partners and ID (numeric). On insert into your join table of partner1, partner2 always make sure they are in numeric order ie if you have partner 1 and 2 always put 1 in the first column if its partners 2 and 1 then you put 1 in the first col and 2 in the second and so on for all the columns you need.

Then to get the unique combinations of partnesr all you need to do is:

Select distinct col1, col1.... from table.

Hope this helps.
Share this answer
Share this answer
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900