May 8, 2019 at 11:52 am
I want to find every combination possible...no duplicates and using a value 1 time per combination.
If there are X values in the scenario, then each combination would contain X values.
Example:
3 values: x, y and z would return:
xyz
xzy
yzx
yxz
zyx
zxy
Examples of want I do not want:
x
xx
xxx
xxy
xxz
I have created a solution with CROSS APPLY / dynamic SQL and it executes fairly quickly until the number of values reaches 9 and greater.
I want to avoid the dynamic SQL.
Thank you in advance
May 8, 2019 at 2:03 pm
Not that it makes too much difference, but what is the input format? Is it a single delimited string, or a recordset?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 8, 2019 at 2:04 pm
single delimited string
May 8, 2019 at 2:49 pm
I assume you can split the string into a table.
WITH cteString (a)
AS
(SELECT a
FROM (VALUES ('X'), ('Y'), ('Z')) x(a)
)
SELECT i.a + j.a + k.a
FROM cteString i
CROSS JOIN cteString j
CROSS JOIN cteString k
WHERE i.a <> j.a
AND i.a <> k.a
AND j.a <> k.a
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 8, 2019 at 2:54 pm
yes, I can split the string into a table.
the number of values will not always be 3....I coded basically what you provided, but with dynamic SQL.
when there are more than 9 values, it executes very slowly.
May 8, 2019 at 3:10 pm
Maybe you could provide the code you have written so we could see why it's taking so long and see if there are any improvements that could be made?
I think it would be difficult to do this in SQL without using dynamic SQL.
If you have n distinct values as the input then there will be n! rows output
You say it slows down when there are more than 9 rows input, well 10! =3,628,800 and when you get to 11 rows it would output nearly 40 million rows. So it is not really surprising that it slows down with the output increasing O(n!).
May 8, 2019 at 3:12 pm
DECLARE @X int
DECLARE @I int
DECLARE @W int
DECLARE @SQL varchar(MAX)
DECLARE @Cols varchar(MAX)
DECLARE @FROM varchar(MAX)
DECLARE @WHERE varchar(MAX)
IF object_id('tempdb..#X') IS NOT NULL
BEGIN
DROP TABLE #X;
END
IF object_id('tempdb..#XX') IS NOT NULL
BEGIN
DROP TABLE #XX;
END
CREATE TABLE #X (
Id int IDENTITY(1,1),
Value varchar(1)
)
CREATE TABLE #XX (
Id int IDENTITY(1,1),
X_Id int,
Value varchar(1),
RowNum int,
TotalRowNum int,
Combo varchar(10)
)
insert into #X(Value)
select 'x'
UNION ALL
select 'y'
UNION ALL
select 'z';
--UNION ALL
--select 'c'
--UNION ALL
--select 't'
--UNION ALL
--select 'w'
--UNION ALL
--select 'l'
--UNION ALL
--select 'a'
--UNION ALL
--select 'b'
--UNION ALL
--select 'd';
SET @X = @@ROWCOUNT;
CREATE CLUSTERED INDEX IX_X_Value on #X (Value);
SET @SQL = 'insert into #XX(X_Id,Value,RowNum,TotalRowNum,Combo)
select X1.Id,X1.Value ,
ROW_NUMBER() OVER (PARTITION BY X1.value ORDER BY X1.Id) AS RowNum,
ROW_NUMBER() OVER (ORDER BY X1.Id) AS TotalRowNum,';
SET @Cols = 'X1.Value';
SET @FROM = 'FROM #X X1';
SET @WHERE = 'WHERE ';
SET @I = 2;
WHILE @I <= @X
BEGIN
SET @Cols = @Cols+'+X'+convert(varchar,@I)+'.Value';
SET @FROM = @FROM+' CROSS APPLY #X X'+convert(varchar,@I);
SET @W = 1
WHILE @W < @I
BEGIN
SET @WHERE = @WHERE+' X'+convert(varchar,@W)+'.Value <> X'+convert(varchar,@I)+'.Value and';
SET @W = @W+1;
END
SET @I = @I+1;
END
IF @X = 1
BEGIN
SET @WHERE = '';
END
ELSE
BEGIN
SET @WHERE = LEFT(@WHERE,LEN(@WHERE) - 4);
END
SET @SQL = @SQL+' '+@Cols+' '+@FROM+' '+@WHERE;
--PRINT @SQL
EXEC (@SQL)
CREATE CLUSTERED INDEX IX_XX_Combo on #XX (Combo);
select Combo
FROM #XX;
DROP TABLE #X;
DROP TABLE #XX;
May 8, 2019 at 3:35 pm
This post may be of interest.
I'll reproduce the code here:
DECLARE @s VARCHAR(25),
@Iteration INT;
SET @s = 'ABCDEF';
SET @Iteration = LEN(@s);
WITH E1 (N)
AS (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),
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;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 8, 2019 at 4:34 pm
thanks for the efforts
May 8, 2019 at 4:53 pm
The code from Google is actually quite a lot slower than the OP's original code.
If you want to keep with your original code there are a few things you can do to increase performance by a small amount:
May 9, 2019 at 6:12 pm
If you don't genuinely need RowNum and TotalRowNum, drop them.
If you do, you can pre-compute RowNum in the X1 table, and compute the TotalRowNum based on the pre-computed RowNums in the X1 table. That should avoid a lot of sorting overhead just to assign row nums.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply