Convertir tablas (resultsets) a cadenas.

El otro día veíamos una forma sencilla de cómo convertir cadenas a tablas para poder tratarlas fácilmente con SQL.Hoy vamos a hacer lo contrario: convertir un ‘resultset’ proveniente de un ‘SELECT’ a una tabla en una cadena de caracteres con un carácter separador.

Este tipo de funcionalidades son normalmente resueltas con funciones que abren un cursor, lo van recorriendo fila a fila y van concatenando los contenidos en un ‘buffer’ que es devuelto al final como retorno.

Pero el SQL puede hacer esto mucho más sencillo y flexible y sin necesidad de utilizar PL/SQL si hacemos uso de la función SYS_CONNECT_BY_PATH, disponible desde la versión 9:

carlosal@db01.xxxxxx> SELECT * FROM V$VERSION
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

carlosal@db01.xxxxxx>   SELECT *
  2      FROM PRUEBA01
  3  ORDER BY ID_N
  4  /

      ID_N C_TXT
---------- -------------------------
         1 UNO
         2 DOS
         3 TRES
         4 CUATRO
         5 CINCO
         6 SEIS
        11 ONCE
        21 VEINTIUNO
        22 VEINTIDOS
        23 VEINTITRES
        24 VEINTICUATRO
        25 VEINTICINCO
        30 TREINTA
        31 TREINTAYUNO
        32 TREINTAYDOS
        33 TREINTAYTRES

16 filas seleccionadas.

Un poco de SYS_CONNECT_BY_PATH por aquí, otro poco de analíticas por allá y…

carlosal@db01.xxxxxx> SELECT SUBSTR(MAX(C_TEXT),2) CADENA
  2     FROM ( SELECT SYS_CONNECT_BY_PATH(C_TXT, ';') C_TEXT
  3              FROM ( SELECT C_TXT,
  4                            ROW_NUMBER() OVER (ORDER BY ID_N) FILA
  5                       FROM PRUEBA01
  6                    )
  7          START WITH FILA = 1
  8    CONNECT BY PRIOR FILA = FILA - 1 )
  9  /

CADENA
----------------------------------------------------------------------------------------------------------------------------------------

UNO;DOS;TRES;CUATRO;CINCO;SEIS;ONCE;VEINTIUNO;VEINTIDOS;VEINTITRES;VEINTICUATRO;VEINTICINCO;TREINTA;TREINTAYUNO;TREINTAYDOS;TREINTAYTRES

¿Chulo eh?

Pero hay más: si queremos agrupar por decenas (por ejemplo) no tendríamos que reescribir una función PL/SQL, sino que cambiando un poco la sentencia tenemos:

carlosal@db01.xxxxxx> SELECT SUBSTR(MAX(C_TEXT),2) CADENA
  2     FROM ( SELECT SYS_CONNECT_BY_PATH(C_TXT, ';') C_TEXT, DECENA
  3              FROM ( SELECT C_TXT,
  4                            ROW_NUMBER() OVER
  5                               (PARTITION BY TRUNC(ID_N, -1)
  6                                ORDER BY ID_N) FILA,
  7                            TRUNC(ID_N, -1) DECENA
  8                       FROM PRUEBA01
  9                    )
 10          START WITH FILA = 1
 11    CONNECT BY PRIOR FILA = FILA - 1 AND PRIOR DECENA = DECENA )
 12    GROUP BY DECENA
 13    ORDER BY DECENA
 14  /

CADENA
-------------------------------------------------------

UNO;DOS;TRES;CUATRO;CINCO;SEIS
ONCE
VEINTIUNO;VEINTIDOS;VEINTITRES;VEINTICUATRO;VEINTICINCO
TREINTA;TREINTAYUNO;TREINTAYDOS;TREINTAYTRES

carlosal@db01.xxxxxx>

Saludos.

Carlos.