Get All the combination - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Get All the combination

Author

Topic

Manothinakaran
Starting Member

8 Posts

Posted - 2010-11-15 : 00:21:33

Dear Guru,

I need a huge favour.
I have a table with column siteid
Data as follows for table sites:
A,B,C

I need to create a view with 2 columns Origin Site, Destination Site.
I need to get all the combination for column siteid.
For an example as below:
A - B
A - C
B - C

I do not want combination as below:
A - A
B - A
B - B
C - C
C - B

Can anyone help me on this?

Sachin.Nand

2937 Posts

Posted - 2010-11-15 : 04:12:37

[code]
declare @tbl table(site varchar(10))
insert @tbl
select 'A' union
select 'B' union
select 'C'

select * from
(
select t1.site ,t2.site site1 from @tbl t1 cross join @tbl t2
)T where site1>site

[/code]

PBUH

Manothinakaran
Starting Member

8 Posts

Posted - 2010-11-22 : 22:04:58

quote:

Originally posted by Sachin.Nand


declare @tbl table(site varchar(10))
insert @tbl
select 'A' union
select 'B' union
select 'C' 

select * from
(
select t1.site ,t2.site  site1  from @tbl t1 cross join @tbl t2
)T where site1>site

PBUH

Dear Guru,

So sorry for the late reply. Thank you sooo much for the help. Really appreciate it alot...thank you soo much

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-22 : 22:14:52

WHY?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources