How to generate a 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, ... series in standard SQL or T-SQL?

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?

Postgres

You can make it work with a single generate_series() and basic math (see mathematical functions).

Wrapped into a simple SQL function:

CREATE OR REPLACE FUNCTION generate_up_down_series(n int, m int)
  RETURNS SETOF int
  LANGUAGE sql IMMUTABLE AS
$func$
SELECT CASE WHEN n2 < n THEN n2 + 1 ELSE n*2 - n2 END
FROM  (
   SELECT n2m, n2m % (n*2) AS n2
   FROM   generate_series(0, n*2*m - 1) n2m
   ) sub
ORDER  BY n2m
$func$;

Call:

SELECT * FROM generate_up_down_series(3, 4);

Generates the desired result. n and m can be any integer where n2m does not overflow int4.
How?

In the subquery:

-
Generate the desired total number of rows (n2m), with a simple ascending number. I name it n2m. 0 to N-1 (not 1 to N) to simplify the following modulo operation.

-
Take it % n*2 (% is the modulo operator) to get a series of n ascending numbers, m times. I name it n2.

In the outer query:

-
Add 1 to lower half (n2

-
For the upper half (n2 >= n) mirror of the lower half with n*2 - n2.

-
I added ORDER BY to guarantee the requested order. With current versions of Postgres it also works without ORDER BY for the simple query - but not necessarily in more complex queries! That's an implementation detail (and it's not going to change) but not mandated by the SQL standard.

Unfortunately, generate_series() is Postgres specific and not standard SQL, as has been commented. But we can reuse the same logic:
Standard SQL

You can generate the serial numbers with a recursive CTE instead of generate_series(), or, more efficiently for repeated use, create a table with serial integer numbers once. Anyone can read, noone can write to it!

CREATE TABLE int_seq (i integer);

WITH RECURSIVE cte(i) AS (
   SELECT 0
   UNION ALL
   SELECT i+1 FROM cte
   WHERE  i < 20000  -- or as many you might need!
   )
INSERT INTO int_seq
SELECT i FROM cte;

Then, the above SELECT becomes even simpler:

SELECT CASE WHEN n2 < n THEN n2 + 1 ELSE n*2 - n2 END AS x
FROM  (
   SELECT i, i % (n*2) AS n2
   FROM   int_seq
   WHERE  i < n*2*m  -- remember: 0 to N-1
   ) sub
ORDER  BY i;

StackExchange Database Administrators Q#160354, answer score: 12