sql - multiple - pivot oracle columns to rows

Construya la tabla de números sobre la marcha en Oracle

Question

¿Cómo devuelvo un conjunto de filas que consta de los últimos cuatro años según la fecha actual?

Si esta consulta se ejecuta el 31/12/2010 debería devolver:

2007
2008
2009
2010

Pero si se ejecuta el 1/1/2011 debería devolver:

2008
2009
2010
2011

Esto es lo que comencé, dos consultas que devuelven el año inicial. Prefiero el segundo porque convertirme en cuerda me parece un poco sucio.

SELECT TO_CHAR(TRUNC(sysdate, ''YY'') - INTERVAL ''3'' YEAR, ''YYYY'') FROM DUAL;
SELECT EXTRACT (YEAR FROM sysdate) - 3 FROM DUAL;

Pero no sé cómo generar filas para completar esto. En SQL Server usaría un CTE como en la función fn_nums en esta página .

Answer 1

Aquí hay una manera:

  SELECT yr
    FROM (    SELECT EXTRACT (YEAR FROM (ADD_MONTHS ( SYSDATE, - ( (LEVEL - 1) * 12)))) yr
                FROM DUAL
          CONNECT BY LEVEL <= 4)
ORDER BY yr;

O:

  SELECT yr
    FROM (    SELECT  EXTRACT (YEAR FROM sysdate) - (level -1 ) yr
                FROM DUAL
          CONNECT BY LEVEL <= 4)
ORDER BY yr;

O:

    SELECT yr
      FROM (SELECT EXTRACT (YEAR FROM SYSDATE) - (x - 1) yr
              FROM DUAL
            MODEL
               DIMENSION BY (1 AS z)
               MEASURES (1 x)
               RULES
                  ITERATE (4)
                  (x [ITERATION_NUMBER] = ITERATION_NUMBER + 1))
  ORDER BY yr;

Answer 2

Para mostrar los números del 1 al 100:

SELECT  LEVEL 
FROM DUAL
CONNECT BY LEVEL <= 100
ORDER BY LEVEL

Para cambiar el máximo, cámbielo en la tercera línea.

Answer 3

De forma similar a la respuesta aceptada, puede reemplazar la vista en línea por una cláusula with. Encuentro que la cláusula with es más legible. En particular, si va a realizar varios cálculos contra la tabla NUMEROS sobre la marcha como parte de la misma consulta, toda la declaración SQL es más legible.

Tabla de números sobre la marcha:

WITH NUMBERS_START_AT_ZERO AS
     (SELECT LEVEL - 1 AS NUM 
       FROM DUAL
       CONNECT BY LEVEL <= 4
       order by NUM desc)
SELECT  EXTRACT (YEAR FROM sysdate) - NUM AS YEARS
  FROM NUMBERS_START_AT_ZERO

Salida:

YEARS
2009
2010
2011
2012