May 18, 2011 at 1:12 pm
GSquared (5/18/2011)
LutzM (5/18/2011)
GSquared (5/18/2011)
I put in the non-repeat bit because of the way Steve wrote his original Where clause. I just moved it into the Joins.Gus: If this concept is expanded to conver the numbers 1 to 5 (and 5 columns), the query would look a little more complicated than the current one. I guess, Jeff is looking for a less complex query (complex = several WHERE or JOIN conditions).
You can actually build the thing dynamically through a string concatenator to use as many copies of the Values table as you want, adding as many columns as you need, without having to build/see the ugly code that happens when you get, say, 20 columns added. It's also less likely to accidentally leave out a join condition (skip from 17 to 19 for example), than coding it by hand.
But that's ugly in its own way, of course.
I guess the dynamic approach is the best solution if there's a requirement to build such a list based on a changing number of columns. However, I'm not sure if T-SQL is the best code to begin with for this task...
May 18, 2011 at 1:18 pm
I don't have time to code and test today, but since the same set of points is being cross joined multiple times, perhaps a recursive CTE?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 18, 2011 at 1:21 pm
Probably needs some work
DECLARE @MaxTally int;
SELECT @MaxTally = 5;
;
WITH
E1(N) AS ( --=== Create Ten 1's
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 --10
),
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E1),
ctefact(N,Factorial) AS ( SELECT N, CAST(1 AS INT) FROM ctetally WHERE N <= @MaxTally
UNION ALL
SELECT t.N, CAST(t.n * f1.Factorial AS INT)
FROM ctefact f1
INNER JOIN cteTally T
ON t.n = f1.n +1
), cteagg (N,Factorial,Agg) AS (
SELECT N,MAX(Factorial),MAX(ct.Agg)
FROM ctefact
CROSS APPLY(SELECT SUM(DISTINCT N) AS Agg FROM cteTally
WHERE N <= @MaxTally) CT
WHERE N <= @MaxTally
GROUP BY N
), ctemax(N,Factorial,Agg) AS (
SELECT N,MAX(factorial),MAX(AGG)
FROM cteagg
WHERE N = @MaxTally
GROUP BY N
)
SELECT t.N AS FirstSlot
,ISNULL(a.n,0) AS SecSlot,ISNULL(b.n,0) AS ThirdSlot,ISNULL(c.n,0) AS FourthSlot,ISNULL(d.n,0) AS FifthSlot
FROM cteagg t
CROSS JOIN cteagg a
CROSS JOIN cteagg b
CROSS JOIN cteagg c
CROSS JOIN cteagg d
CROSS APPLY (SELECT Factorial,Agg FROM ctemax) ct
WHERE ISNULL(a.n,0)+ISNULL(b.n,0)+ISNULL(c.n,0)+ISNULL(d.n,0)+ISNULL(t.n,0) = ct.Agg
AND ISNULL(a.n,1)*ISNULL(b.n,1)*ISNULL(c.n,1)*ISNULL(d.n,1)*ISNULL(t.n,1) = ct.Factorial
ORDER BY t.n,a.n,b.n,c.n
Better would be to allow it to dynamic pivot based on the number of digits in the combo.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 18, 2011 at 1:25 pm
Brandie Tarvin (5/18/2011)
But out of curiosity, what would be your reason for using these combinations? So I could work on a prettier solution.
Strictly curiosity
May 18, 2011 at 1:28 pm
The Dixie Flatline (5/18/2011)
I don't have time to code and test today, but since the same set of points is being cross joined multiple times, perhaps a recursive CTE?
Was thinking that but couldn't wrap my head around it.
May 18, 2011 at 1:30 pm
I didn't exclude matching, but I was thinking along the lines of lottery or ping pong balls. I only have a list of numbers, so if I have 1, 2, 3, 4, 5, I can't double 5 because I don't want to use a sharpie to ruin my objects.
It's more that I was curious about a way that if I gave you a table with x numbers, could you easily build me a result set of combinations. It doesn't matter if it's a single column or multiples, but I'd need X! combinations.
May 18, 2011 at 5:58 pm
Here is another script that will work and is a bit prettier. I am going to work on it a bit more. I found something similar to this on the web and adapted it.
DECLARE @s VARCHAR(25)
,@Iteration Int
SET @s = 'ABCDEFGH';
SET @Iteration = LEN(@s);
WITH E1(N) AS ( --=== Create Ten 1's
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 --10
),
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E1
),CteCombos AS (
SELECT CAST(SUBSTRING(@s, N, 1) AS VARCHAR(25)) AS Token,
CAST('.'+CAST(N AS CHAR(1))+'.' AS VARCHAR(52)) AS Permutation,
CAST(1 AS INT) AS Iteration
FROM cteTally WHERE N <= @Iteration
UNION ALL
SELECT CAST(Token+SUBSTRING(@s, N, 1) AS VARCHAR(25)) AS Token,
CAST(Permutation+CAST(N AS CHAR(1))+'.' AS VARCHAR(52)) AS
Permutation,
s.Iteration + 1 AS Iteration
FROM CteCombos s
INNER JOIN cteTally n
ON s.Permutation NOT LIKE '%.'+CAST(N AS CHAR(1))+'.%'
AND s.Iteration < @Iteration
AND N <= @Iteration
)
SELECT Token,Permutation,Iteration
FROM CteCombos
WHERE Iteration = @Iteration
ORDER BY Permutation
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 19, 2011 at 3:58 pm
OK, not sure I completely understand SQLRNNR's solution, but Jason, that is cool. Tested a few combinations and it seems to work very well.
Thanks.
May 19, 2011 at 4:10 pm
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply