DECLARE @n INT = 1000;

WITH numbers AS (
    SELECT 2 AS num
    UNION ALL
    SELECT num + 1 FROM numbers WHERE num + 1 <= @n
)


,primes_num as (

SELECT *
FROM numbers 

where not EXISTS  (select * from numbers inners where 

numbers.num % inners.num=0 and numbers.num <> inners.num)
)
select STRING_AGG(num,'&') from primes_num


OPTION (MAXRECURSION 1000)

select STRING_AGG(num,'&') from primes

OPTION (MAXRECURSION 1000)

Why this is faling but output is correct

SET SESSION group_concat_max_len = 1000000; WITH RECURSIVE numbers AS ( SELECT 2 AS n UNION ALL SELECT n + 1 FROM numbers WHERE n + 1 <= 1000 ) SELECT GROUP_CONCAT(n SEPARATOR '&') AS prime_numbers FROM numbers WHERE NOT EXISTS ( SELECT 1 FROM numbers AS d WHERE d.n < n AND d.n > 1 AND MOD(n, d.n) = 0 );

This can be refined by only checking divisors upto sqrt(num1), but it works nonetheless (MS SQL Server)

with cte as ( select 2 as n union all select n+1 from cte where n<1000 ), cte2 as ( select c1.n as num1, c2.n as num2, case when c1.n%c2.n=0 then 1 else 0 end as flag from cte c1 cross join cte c2 where c1.n<>c2.n ), primes as ( select distinct num1 from cte2 where num1 not in (select num1 from cte2 where flag=1) ) select string_agg(cast(num1 as varchar),'&') as prime from primes

Took a way more computationally expensive path here, but still works. Learned quite a bit from the other solutions below.

DECLARE @n AS INT = 2
DECLARE @s AS VARCHAR(1000) = ''
WHILE @n < 1000
BEGIN
    DECLARE @m AS INT = @n - 1, @p AS INT = 1
    WHILE @m >= 2 AND @p = 1
    BEGIN
        IF @n % @m <> 0
            SET @m = @m - 1
        ELSE
            SET @p = 0
        END
    IF @p = 1 AND @n > 2
        SET @s = @s + '&' + CAST(@n AS VARCHAR(4))
    ELSE IF @p = 1
        SET @s = @s + CAST(@n AS VARCHAR(4))
    SET @n = @n + 1
END
PRINT @s

WITH RECURSIVE primes as ( Select 2 as n union all select n+1 from primes where n+1<=1000 -- termination statement )

select group_concat(n SEPARATOR '&') from primes x where n not in ( select a from ( Select n1.n as a,n2.n as b from primes n1 inner join primes n2 on n2.n