How to generate a 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, ... series in standard SQL or T-SQL?
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 AS
$func$
SELECT CASE WHEN n2 &lt; 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$  LANGUAGE sql IMMUTABLE;

Call:
SELECT * FROM generate_up_down_series(3, 4);

Generates the desired result. n and m can be any integer where n*2*m does not overflow int4.
How?
In the subquery:

Generate the desired total number of rows (n*2*m), 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 &lt; n).

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

I added ORDER BY to guarantee the requested order. With current versions or 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 warranted 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 &lt; 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 &lt; n THEN n2 + 1 ELSE n*2 - n2 END AS x
FROM  (
   SELECT i, i % (n*2) AS n2
   FROM   int_seq
   WHERE  i &lt; n*2*m  -- remember: 0 to N-1
   ) sub
ORDER  BY i;