This is the dynamic SQL that is created:

SELECT VID, [2022-01-10], [2022-01-11], [2022-01-12]
 ...

Here's another one query solution.

Finished in about 3 seconds for prime numbers up to 1 million (on my machine).

I can't test it against SwePeso's solution as I...

• This reply was modified 3 years, 2 months ago by Jonathan AC Roberts. Reason: Changed last line from [code]AND S.N < SQRT(p.N)[/code] to [code]AND S.N

Very good and a clear explanation of how it works.

CREATE TABLE #t1([VID] [varchar](100) NULL,[OrdDate] [datetime] NOT NULL, [FldVal] int NULL ) ON [PRIMARY]

insert into #t1 values('111','2022-01-10',10)
insert into #t1 values('111','2022-01-11',1)
insert into #t1 values('111','2022-01-12',2)

-- Generate dynamic column names...

Peter "Peso" Larsson wrote a nasty fast Prime Numbers function years ago and I tested it to confirm it.  I'll see if I can find it in my archives...

You only need to test up to the square root of a number to test for primality, which is quite a saving:

declare @n  ...

I think the script in the article is a bit of a non-starter.

It is calculating the square root multiple times for each row.

You only need to test up to the square root of a number to test for primality, which is quite a saving:

declare @n  ...

For exhaustive methods it's quicker to only look at odd numbers.  There was a Youtube video a little while ago where they compared all different programming languages to determine...

I wrote this SQL Server table valued function a few years ago. It returns all the prime numbers less than 1 million in about 1 second.

Thank you, Jonathan. Almost every step in the execution plan now marked as "No Join Predicate" and the whole function now costs 100% with my original tally one...

You don't need to...

;WITH CTE AS 
(
    SELECT oven, 
           T.Hours,
      ...

Got no idea if this will work as you haven't posted any consumable data:

;WITH CTE AS 
(
    SELECT System, 
    ...