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.

3 respuestas a Convertir tablas (resultsets) a cadenas.

  1. Edgar Rojas. dice:

    Hola, buscando otra cosa vi esto. Es sencillamente perfecto, no conocia esta funcion.
    Lo felicito, realmente este tipo de ejemplos son buenisimos, pues sirven de base para otras cosas.

    Por ahi si lo tiene, estoy buscando un ejemplo de como obtener de una tabla plsql un resultset.
    En realidad, ya lo tengo hecho, pero en mi casa, y siempre olvido mirarlo. Esta es la razon por la cual debe estar disponible en la web.

    En caso de que no disponga, si le parece, me agendo y lo publico.

    Gracias.
    Sds y Éxitos.

  2. Henar dice:

    Muchísimas gracias, es justo l oque estaba buscando y me has salvado la vida !!! Feliz Navidad ….

  3. […] y ha sido tratado aquí. Normalmente hay que recurrir a algún tipo de recursividad (incluso en Oracle) y suele resultar un tanto […]

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: