finding all possible combinations

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)

finding all possible combinations

Author

Topic

foolsgold
Starting Member

2 Posts

Posted - 2011-02-25 : 17:23:34

Hi I am brand new to this forum and have a question.

I have a table with data like this


col1   col2
------------
1       a
1       b
1       c
2       x
2       y
3       p

..and what i would like to do is create a result set that creates a comma delimited set of all possible unique combinations, like this:


col1   col2
-------------
1       a
1       b
1       c
1       a, b
1       a, c
1       b, c
2       x
2       y
2       x, y
3       p

in my example "b,a" should not be included as long as "a,b" is there.

any ideas how to achieve this? i need a flexible solution that would support much more than just 3 values in col2.

thanks for your help.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-02-25 : 19:51:28

I bet if you gave $20 to the smart kid in class, he'd give you an answer that looked sorta like this...[CODE]select col1, col2 from MyTable
union all
select a.col1, a.col2 + ', ' + b.col2 as col2
from MyTable a
inner join
MyTable b
on a.Col1 = b.Col1
and a.Col2 < b.Col2[/CODE]You'll need a DISTINCT to cover the case when a.Col2 = b.Col2 but, then again, if the records were allowed to be identical, it wouldn't be relational.

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

foolsgold
Starting Member

2 Posts

Posted - 2011-03-08 : 16:54:13

Thanks for the reply.

One thing I need to add is "all possible combinations" (more than 2).

So same original data

col1 col2
------------
1 a
1 b
1 c
2 x
2 y
3 p

But this time a new additional result row:

col1 col2
-------------
1 a
1 b
1 c
1 a, b
1 a, c
1 b, c
1 a, b, c <-----new row
2 x
2 y
2 x, y
3 p

any help?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-08 : 22:33:26

quote:
Originally posted by foolsgold

Thanks for the reply.

One thing I need to add is "all possible combinations" (more than 2).

So same original data

col1 col2
------------
1 a
1 b
1 c
2 x
2 y
3 p

But this time a new additional result row:

col1 col2
-------------
1 a
1 b
1 c
1 a, b
1 a, c
1 b, c
1 a, b, c <-----new row
2 x
2 y
2 x, y
3 p

any help?

This can be acheived using CTE. Make some attempt and show us what you tried.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources