re: Write a simple but impactful script VIEW POST

FULL DISCUSSION
 

That's with T-SQL using a Tally table

WITH
      L0   AS(SELECT 1 AS C UNION ALL SELECT 1 AS O),         -- 2 rows
      L1   AS(SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), -- 4 rows
      L2   AS(SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B), -- 16 rows
      L3   AS(SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B), -- 256 rows
      L4   AS(SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B), -- 65,536 rows
      Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L4),
      Ord  AS(SELECT TOP 10000 N - 1 AS N FROM Nums ORDER BY N) 

SELECT FORMATMESSAGE('%04i', CAST(N AS INT)) AS N
FROM Ord
ORDER BY NEWID();
 

I'll see you and raise you Postgres :)

SELECT n1.v, n2.v, n3.v, n4.v
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) n1 (v)
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) n2 (v)
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) n3 (v)
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) n4 (v)
ORDER BY random();
 

Works like a charm in MSSQL if random() replaced with newid()! :)

code of conduct - report abuse