Duplicar una solo fila en varias

A pesar de lo que digan mis compañeros â€PRO- JAVA’ del blog , a mi esto me ha parecido de bastante utilidad, así que os lo escribo:

Imaginad que tenéis una tabla con dos registros.

SQL> desc prueba1
 Name                                      Null?    Type
 —————————————– ——– —————————-
 ANNIO                                              VARCHAR2(4)
 MES_INI                                            VARCHAR2(2)
 MES_FIN                                           VARCHAR2(2)

SQL> select * from prueba1;
2008 01 12
2009 01 08
2010 01 10

En cada año tenemos un mes inicial y mes final. Con una sóla select quiero devolver tantas filas como meses hay comprendidos entre ese intervalo.

Esto lo realizamos con una especie de bucle con por el mismo campo. Lo más importante de esta select es poder utilizar la pseudocolumna que nos da el connect by, , con la que podremos limitar el bucle.

SELECT ANNIO,
TO_CHAR(ADD_MONTHS(TO_DATE(MES_INI,’MM’),LEVEL-1),’MM’) MONTH,
LEVEL
FROM PRUEBA1
CONNECT BY PRIOR ANNIO = ANNIO
AND PRIOR MES_INI=MES_INI
AND LEVEL <= TO_NUMBER(MES_FIN)
AND PRIOR .STRING (‘p’, 10) IS NOT NULL

Con esto obtendríamos lo siguiente:

ANNI MO      LEVEL
—- — ———-
2008 01          1
2008 02          2
2008 03          3
2008 04          4
2008 05          5
2008 06          6
2008 07          7
2008 08          8
2008 09          9
2008 10         10
2008 11         11
2008 12         12
2009 01          1
2009 02          2
2009 03          3
2009 04          4
2009 05          5
2009 06          6
2009 07          7
2009 08          8
2010 01          1
2010 02          2
2010 03          3
2010 04          4
2010 05          5
2010 06          6
2010 07          7
2010 08          8
2010 09          9
2010 10         10
 

Que no se nos olvide meter la última condición o nos dará un error tipo:
ORA-01436: CONNECT BY loop in user data

Aquí he encontrado una explicación sobre el paquete dbms_random, por si os interesa:
http://www.adp-gmbh.ch/ora/plsql/dbms_random.html

Otra de las utilidades que he visto a esto, es poder devolver carácter a carácter cada fila:

SQL>  select * from prueba2;
AAA BBB CCC
BBB CCC DDD
DDD EEE FFF

Así podríamos, por ejemplo, contar el número de veces que un determinado carácter aparece en cada fila

SELECT SUBSTR(FILA , LEVEL , 1) as FIL FROM prueba2
CONNECT BY PRIOR FILA = FILA
AND LEVEL <= LENGTH(FILA)
AND PRIOR dbms_random.string (‘a’, 10) IS NOT NULL;
 

FIL
- -
A 
A 
A 
  
B 
B 
B 
  
C 
C 
C 
B 
B 
B 
  
C 
C 
C 

D 
D 
D 
D 
D 
D 
  
E 
E 
E 
  
F 
F 
F 

33 rows selected.

Y la última utilidad que he encontrado, devolver un registro formado por varias palabras una a una:

Creamos una tabla:

create table prueba2 as select ‘ESTOY,DEVOLVIENDO,LA,FILA,POR,PALABRAS’ frase from dual;

Y ahora devolveremos ese registro palabra por palabra:

SQL> select * from prueba2;

FRASE
————————————–
ESTOY,DEVOLVIENDO,LA,FILA,POR,PALABRAS

SELECT SUBSTR (FRASE,
INSTR (FRASE, ‘,’, 1, ROWNUM) + 1,
INSTR (FRASE, ‘,’, 1, ROWNUM+1) – INSTR(FRASE, ‘,’, 1, ROWNUM) -1 ) PALABRAS
FROM ( SELECT ‘,’||FRASE||’,’ FRASE FROM PRUEBA2)
CONNECT BY ROWNUM < LENGTH(FRASE)-LENGTH(REPLACE(FRASE,’,',”))
AND PRIOR DBMS_RANDOM.RANDOM IS NOT NULL

PALABRAS
—————————————-
ESTOY
DEVOLVIENDO
LA
FILA
POR
PALABRAS

Artículos relacionados