Given two numbers n
and m
, I want to generate a series of the form
1, 2, ..., (n-1), n, n, (n-1), ... 2, 1
and repeat it m
times.
For instance, for n = 3
and m = 4
, I want a sequence of the following 24 numbers:
1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1
---------------- ---------------- ---------------- ----------------
I know how to achieve this result in PostgreSQL by either of two methods:
Using the following query, which uses the generate_series
function, and a few tricks to guarantee that the order is the right one:
WITH parameters (n, m) AS
(
VALUES (3, 5)
)
SELECT
xi
FROM
(
SELECT
i, i AS xi
FROM
parameters, generate_series(1, parameters.n) AS x(i)
UNION ALL
SELECT
i + parameters.n, parameters.n + 1 - i AS xi
FROM
parameters, generate_series(1, parameters.n) AS x(i)
) AS s0
CROSS JOIN
generate_series (1, (SELECT m FROM parameters)) AS x(j)
ORDER BY
j, i ;
… or use a function for the same purpose, with adjoint and nested loops:
CREATE FUNCTION generate_up_down_series(
_elements /* n */ integer,
_repetitions /* m */ integer)
RETURNS SETOF integer AS
$BODY$
declare
j INTEGER ;
i INTEGER ;
begin
for j in 1 .. _repetitions loop
for i in 1 .. _elements loop
return next i ;
end loop ;
for i in reverse _elements .. 1 loop
return next i ;
end loop ;
end loop ;
end ;
$BODY$
LANGUAGE plpgsql IMMUTABLE STRICT ;
How could I possibly do the equivalent in either standard SQL or in Transact-SQL / SQL Server?
Best Answer
In Postgres, it's easy using the
generate_series()
function:In standard SQL - and assuming that there is a reasonable limit on the size of the parameters n, m, i.e. less than a million - you can use a
Numbers
table:fill it with the preferred method of your DBMS:
and then use it, instead of
generate_series()
: