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 < 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 < n).

• 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 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 < 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;

In Postgres, it's easy using the generate_series() function:

WITH 
  parameters (n, m) AS
  ( VALUES (3, 5) )
SELECT 
    CASE WHEN g2.i = 1 THEN gn.i ELSE p.n + 1 - gn.i END AS xi
FROM
    parameters AS p, 
    generate_series(1, p.n) AS gn (i),
    generate_series(1, 2)   AS g2 (i),
    generate_series(1, p.m) AS gm (i)
ORDER BY
    gm.i, g2.i, gn.i ;

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:

CREATE TABLE numbers 
( n int not null primary key ) ;

fill it with the preferred method of your DBMS:

INSERT INTO numbers (n)
VALUES (1), (2), .., (1000000) ;  -- some mildly complex SQL here
                                  -- no need to type a million numbers

and then use it, instead of generate_series():

WITH 
  parameters (n, m) AS
  ( VALUES (3, 5) )
SELECT 
    CASE WHEN g2.i = 1 THEN gn.i ELSE p.n + 1 - gn.i END AS xi
FROM
    parameters AS p
  JOIN numbers AS gn (i) ON gn.i <= p.n
  JOIN numbers AS g2 (i) ON g2.i <= 2
  JOIN numbers AS gm (i) ON gm.i <= p.m 
ORDER BY
    gm.i, g2.i, gn.i ;

If you need plain SQL. Theoretically it should to work on the most DBMSs (tested on PostgreSQL and SQLite):

with recursive 
  s(i,n,z) as (
    select * from (values(1,1,1),(3*2,1,2)) as v  -- Here 3 is n
    union all
    select
      case z when 1 then i+1 when 2 then i-1 end, 
      n+1,
      z 
    from s 
    where n < 3), -- And here 3 is n
  m(m) as (select 1 union all select m+1 from m where m < 2) -- Here 2 is m

select n from s, m order by m, i;

Explanation

1. Generate series 1..n

   Assuming that n=3

   with recursive s(n) as (
     select 1
     union all
     select n+1 from s where n<3
   )
   select * from s;
   

   It is quite simple and could be found in the almost any docs about recursive CTEs. However wee need two instances of each values so

2. Generate series 1,1,..,n,n

   with recursive s(n) as (
     select * from (values(1),(1)) as v
     union all
     select n+1 from s where n<3
   )
   select * from s;
   

   Here we just doubling the initial value, which has two rows, but the second bunch we need in the reverse order, so we'll introduce the order in a bit.

3. Before we introduce the order observe that this is also a thing. We can have two rows in the starting condition with three columns each, our n<3 is still a single column conditional. And, we're still just increasing the value of n.

   with recursive s(i,n,z) as (
     select * from (values(1,1,1),(1,1,1)) as v
     union all
     select
       i,
       n+1,
       z 
     from s where n<3
   )
   select * from s;
   

4. Likewise, we can mix them up a bit, watch our starting condition change here: here we have a (6,2), (1,1)

   with recursive s(i,n,z) as (
     select * from (values(1,1,1),(6,1,2)) as v
     union all
     select
       i,
       n+1,
       z 
     from s where n<3
   )
   select * from s;
   

5. Generate series 1..n,n..1

   The trick here is to generate the series, (1..n) twice, and then simply change the ordering on the second set.

   with recursive s(i,n,z) as (
     select * from (values(1,1,1),(3*2,1,2)) as v
     union all
     select
       case z when 1 then i+1 when 2 then i-1 end, 
       n+1,
       z 
     from s where n<3
   )
   select * from s order by i;
   

   Here i is order and z is number of the sequence (or half of sequence if you want). So for sequence 1 we are increasing order from 1 to 3 and for sequence 2 we are decreasing the order from 6 to 4. And finally

6. Multiply the series to m

   (see the first query in the answer)