February 23, 2010 at 12:21 pm
Im trying to generate all distinct number combinations from a stablished set of numbers. This can't be a mashup, the amount of numbers on the combination has a fixed value.
Ex:
DECLARE @TEMP (NR VARCHAR(5))--SO THERES NO NEED FOR A CAST WHEN ADDING '-' OR '/' AND SUCH
INSERT INTO @TEMP
SELECT 3 UNION ALL
SELECT 16 UNION ALL
SELECT 18 UNION ALL
SELECT 24 UNION ALL
SELECT 30 UNION ALL
SELECT 33 UNION ALL
SELECT 40 UNION ALL
SELECT 49 UNION ALL
SELECT 53 UNION ALL
SELECT 58
So, from these i want to build get all distinct combinations as in
3-16-18-24-30-33-40-49
3-16-18-24-30-33-40-53
3-16-18-24-30-33-40-58
but not
3-16-18-24-30-33-40-49
3-16-18-24-30-33-49-40
3-16-18-24-30-40-33-49
and such, as these are not distinct combinations.
I currently have no idea how to do it so any help is appreciated
--
Thiago Dantas
@DantHimself
February 23, 2010 at 1:21 pm
1.How many parameters can there be in the table (there is 10 right now). Can we have more, or less numbers in the table?
2.How many of these parameters must be used to create the distinct values? There is 8 right now, do you always want 8?
We need as much information as possible to help,
Cheers,
J-F
February 23, 2010 at 1:25 pm
there are 10 parameters now, this can change but not much, should be around 6-12 tops.
I used 8, this could be 6-9 but I prefer to do it with a fixed amount of different numbers.
All parameters supplied should be used
--
Thiago Dantas
@DantHimself
February 23, 2010 at 3:00 pm
If the amount of numbers is fixed here's an example of 8 numbers. I doesn't look efficient but given the number of parameters it will do. I have changed the data type of the number column to int to make the joins easier.
DECLARE @TEMP TABLE (NR INT)
INSERT INTO @TEMP
SELECT 3 UNION ALL
SELECT 16 UNION ALL
SELECT 18 UNION ALL
SELECT 24 UNION ALL
SELECT 30 UNION ALL
SELECT 33 UNION ALL
SELECT 40 UNION ALL
SELECT 49 UNION ALL
SELECT 53 UNION ALL
SELECT 58
SELECT
CAST(T1.NR AS VARCHAR) + '-' + CAST(T2.NR AS VARCHAR) + '-' + CAST(T3.NR AS VARCHAR) + '-' +
CAST(T4.NR AS VARCHAR) + '-' + CAST(T5.NR AS VARCHAR) + '-' + CAST(T6.NR AS VARCHAR) + '-' +
CAST(T7.NR AS VARCHAR) + '-' + CAST(T8.NR AS VARCHAR) FROM @TEMP T1
JOIN
( SELECT NR FROM @TEMP) T2 ON T2.NR > T1.NR
JOIN
( SELECT NR FROM @TEMP) T3 ON T3.NR > T2.NR
JOIN
( SELECT NR FROM @TEMP) T4 ON T4.NR > T3.NR
JOIN
( SELECT NR FROM @TEMP) T5 ON T5.NR > T4.NR
JOIN
( SELECT NR FROM @TEMP) T6 ON T6.NR > T5.NR
JOIN
( SELECT NR FROM @TEMP) T7 ON T7.NR > T6.NR
JOIN
( SELECT NR FROM @TEMP) T8 ON T8.NR > T7.NR
ORDER BY
T1.NR, T2.NR, T3.NR, T4.NR, T5.NR, T6.NR, T7.NR, T8.NR
Peter
February 23, 2010 at 3:26 pm
Thanks alot Peter, thats exactly what I was looking for.
I tried achieving this with CROSS JOINS but got very very far away from expected results
--
Thiago Dantas
@DantHimself
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply