Creating all possible combinations of two numbers of two columns without recursion or repeatation.

Question

Creating all possible combinations of two numbers of two columns without recursion or repeatation.

rockybhai

SSCommitted

Points: 1574
More actions
March 8, 2012 at 7:21 am

#258371

I have got two columns. I need to create a possible combinations out of it without recursion. (Cross join is not allowed)
For Example:
S_ID D_ID
50 47,48,49,54
53 47,48,49
Result would be like this: For the first S_ID = 50
Combination
50,47
50,48
50,49
50,54
Result would be like this: For the second S_ID = 53
Combination
53,47
53,48
53,49
Provided S_ID is a single number and D_ID can be single number or many numbers separated by commas.
Thanks in advance. 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Why is cross join not allowed?

For the comma-delimited list, look in the scripts library here for a string split function. I'd then cross apply the function to the table containing the comma-delimited list and filter for the acceptable values.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

GilaMonster (3/8/2012)
Why is cross join not allowed?
For the comma-delimited list, look in the scripts library here for a string split function. I'd then cross apply the function to the table containing the comma-delimited list and filter for the acceptable values.

I guess, because it's another challenge, given to OP to solve...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Cross join doesn't even make sense here. It is a single non-normalized table with two columns.

As Gail said, the best approach is to find a string splitter and cross apply. But where can you find a string splitter??? (hint: look at my signature)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

R.P.Rozema SSChampion Points: 12317 More actions · Answer 4

If an exam question explicitly states "cross join is not allowed", they're probably asking for you to show that there is another 'construct' in SQL that accomplishes the same result as a cross join...

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

venoym SSCarpal Tunnel Points: 4161 More actions · Answer 5

A split function will work well. Keep in mind that in compatibility for SQL 2000 you can't always use a function on a column. So you may have to retro fit the function into an inline derived table or some such.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

venoym (3/8/2012)
A split function will work well. Keep in mind that in compatibility for SQL 2000 you can't always use a function on a column. So you may have to retro fit the function into an inline derived table or some such.

Given that this has all the appearance of homework AND it is posted in the 2008 forum I don't think anybody is too worried about 2000 compatibility. 🙂

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/