# Recursive cross join to get all available combinaisons

• Ninja's_RGR'us (4/9/2010)

Any luck on the clr routine?

Been busy on another thread - writing a SQLCLR routine to return the phase of the moon for a given date :w00t:

Blame Lowell. I'll get to this soon.

• Paul White NZ (4/9/2010)

Ninja's_RGR'us (4/9/2010)

Any luck on the clr routine?

Been busy on another thread - writing a SQLCLR routine to return the phase of the moon for a given date :w00t:

Blame Lowell. I'll get to this soon.

No hurries, I was just curious...

• While revisiting the Numbers table solution, I discovered that the following code creates a table from 20 names in 6 seconds on my old laptop:

`USE tempdb;`

`GO`

`IF OBJECT_ID(N'temp..#t', N'U')`

` IS NOT NULL`

` DROP TABLE #t;`

`GO`

`SELECT N,`

` list =`

` (`

` CASE WHEN N & 1 = 1 THEN 'Alice,' ELSE '' END +`

` CASE WHEN N & 2 = 2 THEN 'Bob,' ELSE '' END +`

` CASE WHEN N & 4 = 4 THEN 'Carol,' ELSE '' END +`

` CASE WHEN N & 8 = 8 THEN 'Dan,' ELSE '' END +`

` CASE WHEN N & 16 = 16 THEN 'Eric,' ELSE '' END +`

` CASE WHEN N & 32 = 32 THEN 'Frank,' ELSE '' END +`

` CASE WHEN N & 64 = 64 THEN 'George,' ELSE '' END +`

` CASE WHEN N & 128 = 128 THEN 'Harry,' ELSE '' END +`

` CASE WHEN N & 256 = 256 THEN 'Ian,' ELSE '' END +`

` CASE WHEN N & 512 = 512 THEN 'John,' ELSE '' END +`

` CASE WHEN N & 1024 = 1024 THEN 'Kevin,' ELSE '' END +`

` CASE WHEN N & 2048 = 2048 THEN 'Lisa,' ELSE '' END +`

` CASE WHEN N & 4096 = 4096 THEN 'Mandy,' ELSE '' END +`

` CASE WHEN N & 8192 = 8192 THEN 'Norman,' ELSE '' END +`

` CASE WHEN N & 16384 = 16384 THEN 'Oscar,' ELSE '' END +`

` CASE WHEN N & 32768 = 32768 THEN 'Paul,' ELSE '' END +`

` CASE WHEN N & 65536 = 65536 THEN 'Quincy,' ELSE '' END +`

` CASE WHEN N & 131072 = 131072 THEN 'Roger,' ELSE '' END +`

` CASE WHEN N & 262144 = 262144 THEN 'Sam,' ELSE '' END +`

` CASE WHEN N & 524288 = 524288 THEN 'Terri,' ELSE '' END`

` )`

`INTO #t`

`FROM dbo.Numbers(POWER(2, 20) - 1);`

• Paul White NZ (4/10/2010)

While revisiting the Numbers table solution, I discovered that the following code creates a table from 20 names in 6 seconds on my old laptop:

`USE tempdb;`

`GO`

`IF OBJECT_ID(N'temp..#t', N'U')`

` IS NOT NULL`

` DROP TABLE #t;`

`GO`

`SELECT N,`

` list =`

` (`

` CASE WHEN N & 1 = 1 THEN 'Alice,' ELSE '' END +`

` CASE WHEN N & 2 = 2 THEN 'Bob,' ELSE '' END +`

` CASE WHEN N & 4 = 4 THEN 'Carol,' ELSE '' END +`

` CASE WHEN N & 8 = 8 THEN 'Dan,' ELSE '' END +`

` CASE WHEN N & 16 = 16 THEN 'Eric,' ELSE '' END +`

` CASE WHEN N & 32 = 32 THEN 'Frank,' ELSE '' END +`

` CASE WHEN N & 64 = 64 THEN 'George,' ELSE '' END +`

` CASE WHEN N & 128 = 128 THEN 'Harry,' ELSE '' END +`

` CASE WHEN N & 256 = 256 THEN 'Ian,' ELSE '' END +`

` CASE WHEN N & 512 = 512 THEN 'John,' ELSE '' END +`

` CASE WHEN N & 1024 = 1024 THEN 'Kevin,' ELSE '' END +`

` CASE WHEN N & 2048 = 2048 THEN 'Lisa,' ELSE '' END +`

` CASE WHEN N & 4096 = 4096 THEN 'Mandy,' ELSE '' END +`

` CASE WHEN N & 8192 = 8192 THEN 'Norman,' ELSE '' END +`

` CASE WHEN N & 16384 = 16384 THEN 'Oscar,' ELSE '' END +`

` CASE WHEN N & 32768 = 32768 THEN 'Paul,' ELSE '' END +`

` CASE WHEN N & 65536 = 65536 THEN 'Quincy,' ELSE '' END +`

` CASE WHEN N & 131072 = 131072 THEN 'Roger,' ELSE '' END +`

` CASE WHEN N & 262144 = 262144 THEN 'Sam,' ELSE '' END +`

` CASE WHEN N & 524288 = 524288 THEN 'Terri,' ELSE '' END`

` )`

`INTO #t`

`FROM dbo.Numbers(POWER(2, 20) - 1);`

Wow that's fast... under 3 secs on our prod server.

Only 3 more secs to shave off boys ! 😉

• Ninja's_RGR'us (4/10/2010)

Wow that's fast... under 3 secs on our prod server.

Only 3 more secs to shave off boys ! 😉

If my math skills are anywhere near where I think they are, you now want the routine to go back in time? DBCC TimeWarp?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

• Ninja's_RGR'us (4/10/2010)

Wow that's fast... under 3 secs on our prod server.

• DBCC TimeWarp would be so usefull for this one :-D.

• Ninja's_RGR'us (4/9/2010)

-- Maxrows = 16: (65,535 row(s) affected) / 00:00:02

-- Maxrows = 20: (1,048,575 row(s) affected) / 00:00:46

-- Maxrows = 21: (2,097,151 row(s) affected) / 00:01:33

-- Maxrows = 22: (4,194,303 row(s) affected) / 00:03:08

I'm not sure I'm using it right...

-- 16 : 65518

-- 20 : 1048554

That's the WHERE clause, which filters out the single row in the result set which contains all values plus other rows which contain only a single value.

Maxrows = 16: (65,535 row(s) affected) without the WHERE clause

16 : 65518 with the WHERE clause filtering out 16+1 rows

Heh in any case, it's a little sluggish compared to Paul's code 😉

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Chris Morris-439714 (4/12/2010)

Heh in any case, it's a little sluggish...

But full of recursive awesomeness!!!

Great code.

• Paul White NZ (4/12/2010)

Chris Morris-439714 (4/12/2010)

Heh in any case, it's a little sluggish...

But full of recursive awesomeness!!!

Great code.

But so slow! I've got a little library of rCTE's now for a variety of jobs, but they're mostly code curiosities which have quicker equivalents using more conventional TSQL - no opportunity yet to say "the fastest solution for this is a rCTE".

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Nice examples.

IMO for a very temporary solution it still may be tolerable ... to use a hybrid solution

`CREATE function ufn_GetOrderDetailsCombinations ( @PurchaseOrderID int )`

`returns table`

`as`

` return ( `

`/*`

`Select *`

`from ufn_GetOrderDetailsCombinations ( 167 )`

`-- MUST USE MAXRECURSION IN QUERY !`

`OPTION (MAXRECURSION 0)`

`*/`

`with cteOrderDetailSeq`

` as ( --Minimize the data scope as soon as possible !`

` SELECT POD.PurchaseOrderID`

` , convert(varchar(5000), POD.PurchaseOrderDetailID) + ' (' + convert(char(10), POH.ShipDate, 121) + ')' as OrderDetailInfo`

` , rank() over ( partition by POD.PurchaseOrderID order by POD.PurchaseOrderID, POD.PurchaseOrderDetailID ) as SEQNR`

` FROM [AdventureWorks].[Purchasing].[PurchaseOrderHeader] POH`

` INNER JOIN [AdventureWorks].[Purchasing].[PurchaseOrderDetail] POD`

` on POD.PurchaseOrderID = POH.PurchaseOrderID`

` Where POH.PurchaseOrderID = @PurchaseOrderID`

` ) ,`

` cteMaxMembers`

` as (`

` Select count(*) as MaxMembers`

` from cteOrderDetailSeq`

` ) ,`

` cteCompositions`

` AS ( -- Chris Morris-439714 calculator cte`

` SELECT ColumnNo = 1`

` , Number = 1`

` , Store = CAST(' 1' AS VARCHAR(3000))`

` UNION ALL`

` SELECT ColumnNo = CASE WHEN lr.Number = tr.MaxRows THEN lr.ColumnNo - 1`

` ELSE lr.ColumnNo + 1`

` END`

` , Number = CASE WHEN lr.Number = tr.MaxRows`

` -- go back one 3-char column position, increment number found there`

` THEN CAST(RIGHT(LEFT(lr.Store, ( lr.ColumnNo - 1 ) * 3), 3) AS INT) + 1`

` ELSE lr.Number + 1`

` END`

` , Store = CASE WHEN lr.Number = tr.MaxRows THEN CAST(LEFT(lr.Store, ( lr.ColumnNo - 2 ) * 3) -- < chop off last two 3-char column positions`

` + RIGHT(' ' + LTRIM(STR( -- < add new Number`

` CAST(RIGHT(LEFT(lr.Store, ( lr.ColumnNo - 1 ) * 3), 3) AS INT) + 1)), 3) AS VARCHAR(3000))`

` ELSE CAST(lr.Store + RIGHT(' ' + LTRIM(STR(lr.Number + 1)), 3) AS VARCHAR(3000))`

` END`

` FROM cteCompositions lr`

` INNER JOIN (`

` Select MaxMembers as MaxRows`

` from cteMaxMembers`

` ) tr`

` ON NOT (`

` lr.ColumnNo = 1`

` AND lr.Number = tr.MaxRows`

` )`

` ) ,`

` cteCompose`

` as (`

` Select ODS.PurchaseOrderID`

` , ODS.OrderDetailInfo`

` , C.Store`

` from cteCompositions C`

`/* replace spaces with split character */`

` cross apply master.dbo.fn_ALZDBA_Split(replace(replace(C.Store, ' ', ';'),' ',';'), ';') S`

` inner join cteOrderDetailSeq ODS`

` on ODS.SEQNR = S.item`

` )`

` Select C.PurchaseOrderID`

` , STUFF((`

` SELECT COALESCE(',' + S1.OrderDetailInfo, '')`

` FROM cteCompose S1`

` WHERE S1.PurchaseOrderID = C.PurchaseOrderID`

` and S1.Store = C.Store`

` FOR`

` XML PATH('')`

` ), 1, 1, '') as OrderDetails`

` from cteCompose C`

` group by C.PurchaseOrderID`

` , C.Store`

` ) ;`

` go`

`Select *`

`from ufn_GetOrderDetailsCombinations ( 167 )`

`OPTION (MAXRECURSION 0)`

`go`

`/*`

`(63 row(s) affected)`

` CPU time = 172 ms, elapsed time = 409 ms.`

`*/`

`Select *`

`from ufn_GetOrderDetailsCombinations ( 455 )`

`OPTION (MAXRECURSION 0)`

`go`

`/*`

`(1023 row(s) affected)`

` CPU time = 4766 ms, elapsed time = 8744 ms.`

`*/`

`/*`

`PurchaseOrderIDnDetailsnCombinations`

`1015301073741823`

`1352667108863`

`12552416777215`

`515201048575`

`68519524287`

`76517131071`

`8351665535`

`13051532767`

`20451416383`

`1905138191`

`325124095`

`645112047`

`455101023`

`1859511`

`2958255`

`27663`

`8531`

`11415`

`737`

`223`

`111`

`*/`

Although an enduser may not be very happy if (s)he has to pick one out of 1023 😉

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems

press F1 for solution, press shift+F1 for urgent solution 😀

Who am I ? Sometimes this is me but most of the time this is me

• Thanks a million to all who've helped on this problem.

I've had a very happy conclusion yesterday. After being told but the erp cie that it was impossible to add the field we needed and hence have the correct design, I've found out about 2 other ledgers in the system and if I cross both of them I can get the actual document # for each line of the invoices and shippings. That means that I can simply do a distinct in a derived table + concatenation which gives the results well under 1 sec for even 10 000 documents.

Maybe there's a God after all :w00t:.

• Always nice to get a happy ending 🙂

• I may be way off... but this seems like a simple binary problem to me.

`DECLARE @People TABLE (Person VARCHAR(50))`

`INSERT INTO @People VALUES ('Ted')`

`INSERT INTO @People VALUES ('Tom')`

`INSERT INTO @People VALUES ('Mary')`

`INSERT INTO @People VALUES ('Sue')`

`--INSERT INTO @People VALUES ('Frank')`

`; WITH --"Inline" CTE Driven "Tally Table" produces values up to 10K... enough to cover VARCHAR(8000).`

` E1(N) AS ( --=== Create Ten 1's very quickly`

` SELECT N`

` FROM (SELECT 1 N0, 1 N1, 1 N2, 1 N3, 1 N4, 1 N5, 1 N6, 1 N7, 1 N8, 1 N9) AS E0`

` UNPIVOT (N FOR Nx IN (N0, N1, N2, N3, N4, N5, N6, N7, N8, N9)) AS unpvt`

` ), --10`

` E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100`

` --E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000`

`cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E2)`

`SELECT N, Person`

`FROM cteTally`

`JOIN (SELECT Person, ROW_NUMBER() OVER(ORDER BY Person) R, POWER(2,ROW_NUMBER() OVER(ORDER BY Person)-1) B /* Bitflag - I am sure there is a better way to do this */`

`FROM @People) P`

`ON P.B & N = P.B`

`WHERE N <= POWER(2,(SELECT COUNT(*) FROM @People))`

`ORDER BY N`

And then you have your list. All you would have to do is at your count filter or whatever you needed to do with this data set.

Hope it helps!

(p.s. Just saw a post from Jeff Moden on the tally CTE... had to give it a try... thanks Jeff! 🙂 )

• I tried with those values... and I got only 319 rows back... only 1M short of the correct answer

DECLARE @People TABLE (Person VARCHAR(50))

INSERT INTO @People VALUES ('0')

INSERT INTO @People VALUES ('1')

INSERT INTO @People VALUES ('2')

INSERT INTO @People VALUES ('3')

INSERT INTO @People VALUES ('4')

INSERT INTO @People VALUES ('5')

INSERT INTO @People VALUES ('6')

INSERT INTO @People VALUES ('7')

INSERT INTO @People VALUES ('8')

INSERT INTO @People VALUES ('9')

INSERT INTO @People VALUES ('10')

INSERT INTO @People VALUES ('11')

INSERT INTO @People VALUES ('12')

INSERT INTO @People VALUES ('13')

INSERT INTO @People VALUES ('14')

INSERT INTO @People VALUES ('15')

Viewing 15 posts - 46 through 60 (of 64 total)