combinations - 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

SQL Server 2000 Forums

Transact-SQL (2000)

combinations

Author

Topic

digitalfront
Starting Member

4 Posts

Posted - 2007-09-13 : 15:31:08

I have a table containing 2 columns, "group" and "values". For example:

group value
A 100
A 101
B 200
C 300
C 301
C 302

my question is, how can create a table of all possible combinations of these values (one value from each group makes a combination)?

ie. combination1 = 100, 200, 300

thanks for your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 15:33:54

Have you tried any JOIN yet?

E 12°55'05.25"
N 56°04'39.16"

digitalfront
Starting Member

4 Posts

Posted - 2007-09-13 : 15:34:41

join to what exactly?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 15:42:12

[code]-- Prepare sample data
DECLARE @Sample TABLE (Grp CHAR(1), Value INT)

INSERT @Sample
SELECT 'A', 100 UNION ALL
SELECT 'A', 101 UNION ALL
SELECT 'B', 200 UNION ALL
SELECT 'C', 300 UNION ALL
SELECT 'C', 301 UNION ALL
SELECT 'C', 302

-- Show the expected output
SELECT s1.Value,
s2.Value,
s3.Value
FROM @Sample AS s1
INNER JOIN @Sample AS s2 ON s2.Grp > s1.Grp
INNER JOIN @Sample AS s3 ON s3.Grp > s2.Grp
ORDER BY s1.Value,
s2.Value,
s3.Value[/code]

E 12°55'05.25"
N 56°04'39.16"

digitalfront
Starting Member

4 Posts

Posted - 2007-09-13 : 15:43:49

ok i see what you mean now. now to perform this query for an unknown number of groups, would the best way just be to build query dynamically somehow?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 15:59:01

Yes.
Here is a link to a similar topic where you can see how dynamic sql statements of this kind is built.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86864

But beware of SQL injection! In the example in the link, there is no possibility of SQL injection but in your case it might be a problem.

E 12°55'05.25"
N 56°04'39.16"

digitalfront
Starting Member

4 Posts

Posted - 2007-09-13 : 16:07:23

thank you so much for your help. i was trying all these convoluted solutions using cursors and loops and getting nowhere. i knew there was a more elegant solution.

zam1030
Starting Member

6 Posts

Posted - 2007-09-14 : 06:18:35

Can you try this?

Select distinct group,'TMP' as Tmpfield Into TmpGroup from Table
Select distinct Value,'TMP' as TmpField Into TmpValue from Table

Select *
from TmpGroup alias Group
Join TmpValue alias Value
on Group.TmpField=Value.TmpField

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources