JASoft.org

El blog de José Manuel Alarcón Aguín. Programación web y mucho más...

MENÚ - JASoft: JM Alarcón

Cómo devolver los resultados de una sub-consulta como un único campo de texto

cuadricula_datosEcha un vistazo a una versión más moderna y más detallada de este artículo.

Esta es una tarea bastante común pero que es muy complicada de conseguir con SQL Server.

Imagínate que tienes la siguiente situación: Necesitas obtener un listado, en una cuadricula, con los datos básicos de cada cliente de tu base de datos, y con la fecha y el importe de cada pedido que ha realizado en el último año a continuación en la misma fila.

Obtener un listado de clientes es un simple SELECT y obtener un listado de clientes con sus pedidos es un simple INNER JOIN. Lo complicado es que obtengamos para cada cliente toda la información de los pedidos como si fuera un campo más de la consulta. Cada cliente puede tener (y tendrá generalmente) un número de pedidos distinto: unos harán 1 pedido, otros 3 y otros 10, así que no podemos simplemente trasponer los resultados con PIVOT o UNPIVOT para devolverlos en columnas en lugar de en filas.

Un listado de este tipo tendría un aspecto similar a este:

FORXML_PedidosEjemplo

Es decir, si nos fijamos, en el HTML resultante cada fila tiene un número variable de columnas, de modo que un cliente tiene 3  pedidos (y por tanto 6 celdas más), otro solamente 1 pedido, otros 2, etc...

Si tenemos muchos clientes, lanzar desde el lado cliente una consulta extra por cada registro para obtener los sub-pedidos y encajarlos en los resultados es algo muy costoso y hará que perdamos rendimiento. Una opción más común y efectiva sería obtener una lista de todos los pedidos de cada cliente e iterar por ellos cambiando de fila cuando detectemos que cambia el cliente en el resulset de la base de datos.

Sin embargo en ocasiones sería mucho mejor poder lanzar una única consulta que nos devuelva de alguna manera ese dato variable ya listo para ser utilizado en nuestra aplicación.

Para ver cómo conseguir esto último, utilizaremos la archiconocida base de datos de ejemplo Northwind.

Primero las consultas básicas

Si queremos obtener todos los pedidos de cada cliente, basta hacer una consulta sencilla de combinación de tablas con INNER JOIN, así:

   1: SELECT     dbo.Customers.CompanyName, dbo.Orders.OrderID, dbo.Orders.OrderDate
   2: FROM         dbo.Customers INNER JOIN
   3:                       dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
   4: ORDER BY CompanyName ASC

Nota: Por simplicidad he elegido el ID del pedido en lugar de su importe por evitar una sub-consulta más para sumarlos, ya que solo liaría la sintaxis, es muy fácil y no es objeto de este post.

Con esto obtenemos el siguiente resultado en la base de datos Northwind:

FORXML_INNERJOIN

Es decir, una línea en el resulset por cada pedido del cliente, con el nombre del cliente repetido.

Lo que necesitamos sin embargo es que cada pedido se junte de alguna manera para obtenerlos todos juntos dentro de un campo de resultados, obteniendo así una única fila por cliente.

Para conseguirlo vamos a separar el proceso en dos partes

Separando la consulta en dos partes

Lo que necesitamos por lo tanto es una consulta similar a esta tan sencilla:

   1: SELECT CompanyName ,
   2:         (
   3:             --ALGO AQUÍ
   4:         ) AS Pedidos
   5: FROM Customers
   6: ORDER BY CompanyName ASC

Es decir, tendremos que introducir alguna instrucción en el hueco que hemos dejado para que nos devuelva los pedidos como un único resultado. Eso implica devolverlos concatenados de alguna manera, generalmente como una cadena de texto, claro está.

La sub-consulta que necesitamos para obtener los pedidos de cada cliente es obvia también, sería esta:

   1: SELECT  OrderID, OrderDate FROM Orders 
   2:             WHERE CustomerID = Customers.CustomerID

Que utilizada dentro de la anterior nos daría los pedidos de cada cliente en un resulset. Lo que ocurre es que no nos devuelve un único dato, sino que nos devuelve un conjunto de registros, que no podemos introducir como si fuera un campo más de resultado. Hay que juntarlos en una cadena de alguna manera.

Podríamos probar con algo así:

   1: SELECT  CAST(OrderID AS nvarchar(20)) + ' - ' + CAST(OrderDate AS NVARCHAR(20)) FROM Orders 
   2:             WHERE CustomerID = Customers.CustomerID

De este modo obtendríamos un único campo por cada registro devuelto por la sub-consulta, pero sigue sin servirnos porque si un cliente tiene 5 pedidos obtendríamos 5 registros como resultado, y necesitamos imperiosamente obtener solamente 1 para que la consulta funcione.

FOR XML al rescate

Cuando alguien inventa una herramienta con un propósito en mente, generalmente se sorprende de los usos que luego le da la gente y en los que jamás había pensado. Me da la impresión de que este es el caso de como se siente el que inventó la instrucción FOR XML de SQL Server, introducida con SQL Server 2005.

FOR XML, como puede deducirse de su nombre, sirve para convertir un conjunto de resultados en una expresión XML.

Dispone de cuatro variantes:

  • RAW: genera un elemento de tipo <row> por cada registro, poniendo sus valores como atributos de cada nodo.
  • AUTO: genere los nodos XML a partir de los nombres de los campos y de la tabla, de manera automática.
  • EXPLICIT: deja definir cómo queremos el XML resultante usando una serie de convenciones en los nombres de las tablas y los campos usados en las consultas.
  • PATH: Es parecido al anterior pero mucho más fácil de usar, aunque con algunas limitaciones. Es el que mejor nos va a venir para nuestro propósito.

¿Cómo podemos sacar partido a esta instrucción para conseguir nuestro propósito?

Usando FOR XML PATH podemos controlar de qué forma queremos devolver los resultados de la sub-consulta agregados en un único campo.

Por ejemplo, si escribimos lo siguiente para rellenar el hueco en la consulta del apartado anterior:

   1: SELECT CompanyName ,
   2:         (
   3:             SELECT  CAST(OrderID AS nvarchar(20)) + ' - ' + CAST(OrderDate AS NVARCHAR(20)) AS td FROM Orders
   4:             WHERE CustomerID = Customers.CustomerID
   5:             FOR XML PATH('tr')
   6:         ) AS Pedidos
   7: FROM Customers
   8: ORDER BY CompanyName ASC

Con el FOR XML le estamos indicando que queremos devolver los resultados de la sub-consulta como una cadena de texto XML que tiene como nodo para cada registro uno de nombre “tr” (lo que va entre paréntesis en el FOR XML PATH), y como sub-nodo para el campo que devolvemos el nombre “td”, por lo que obtendremos el siguiente resultado:

FORXML_TRTD
Pulsa para aumentar

Como puedes observar en la figura anterior, nos devuelve una cadena con filas y columnas de una tabla HTML, lo cual es perfecto para nuestro propósito, y podemos insertar el campo sin más en los resultados de una página para obtener una tabla de datos como la que queríamos.

Control libre del formato del resultado

Esto está muy bien, pero nos da poca libertad en el formato del resultado en ese campo. ¿Cómo podemos generar un texto cualquiera a partir de los campos del resultado de la sub-consulta?

Pues simplemente concatenando los campos como mejor nos venga y haciendo que el FOR XML no genere automáticamente ningún nodo XML.

Para ello lo que debemos hacer es indicar como ruta base del XML una cadena vacía: esto hará que el XML resultante no utilice nodo alguno para rodear cada registro. Si además no le ponemos nombre al campo que devolvemos en la sub-consulta tampoco se generará nodo alguno para éste. Así que si hacemos esto:

   1: SELECT CompanyName ,
   2:         (
   3:             SELECT  ' // ' + CAST(OrderID AS nvarchar(20)) + ' - ' + CAST(OrderDate AS NVARCHAR(20)) FROM Orders
   4:             WHERE CustomerID = Customers.CustomerID
   5:             FOR XML PATH('')
   6:         ) AS Pedidos
   7: FROM Customers
   8: ORDER BY CompanyName ASC

Obtendremos el siguiente resultado:

FORXML_FormatoLibre

Fíjate en como cada registro de la sub-consulta se separa de los demás mediante un “ // “ al principio , y cada campo del registro se separa del otro con un guión. Podríamos haber usado cualquier otro formato más elaborado, claro está.

Sólo queda un pequeño problema: librarnos del primer separador de registros (“ // “) que aparece al principio de cada campo y que nos está molestando justo ahí. Es decir, debería estar entre cada registro pero no en el primero. ¿Cómo lo eliminamos?

Eliminando el primer separador con STUFF

TSQL tiene una instrucción llamada STUFF que nos permite rellenar (es su traducción del inglés) cualquier cadena con otra cadena diferente, y toma los siguientes parámetros:

  • La cadena origen en la que queremos hacer el “rellenado”
  • La posición en la que empezaremos a “rellenar”
  • La cantidad de caracteres que vamos a retirar para hacer sitio para nuestra nueva cadena de "relleno”
  • La cadena de “relleno”

Gracias a esta instrucción librarnos de esos caracteres del principio es muy sencillo pues basta añadir STUFF de la siguiente manera:

   1: STUFF(expresion, 1, 4, '')

Siendo “expresion” la expresi´no en la que vamos a sustituir, en nuestro caso la consulta anterior, de la que eliminamos cuatro caracteres desde la primera posición (o sea, los cuatro de “ // “), y los sustituimos por una cadena en blanco, o sea por nada: los quitamos.

Así, nuestra expresión final queda de la siguiente manera:

   1: SELECT CompanyName ,
   2:         STUFF(
   3:               (
   4:                 SELECT  ' // ' + CAST(OrderID AS nvarchar(20)) + ' - ' + CAST(OrderDate AS NVARCHAR(20))
   5:                 FROM Orders
   6:                 WHERE CustomerID = Customers.CustomerID
   7:                 FOR XML PATH('')
   8:               ),
   9:               1,
  10:               4,
  11:               ''
  12:             ) AS Pedidos
  13: FROM Customers
  14: ORDER BY CompanyName ASC

Obteniendo el siguiente resultado:

FORXML_FormatoLibreSTUFF

que, como vemos, es el esperado.

En resumen

Utilizando un par de comandos de T-SQL en principio orientados a otro tipo de problemas, es posible conseguir cómodos listados de datos de sub-consultas, en una sola consulta, obteniendo el formato que deseemos.

Es algo poco común pero que en ciertas ocasiones puede resultarnos muy útil. Yo he tenido que utilizarlo hace poco y me lo he pasado muy bien aprendiendo estas técnicas que ahora te explico con detalle aquí.

Lleva tus conocimientos de SQL Server y T-SQL a otro nivel con nuestro curso.
Especialista en programación de bases de datos con SQL Server

¡Espero que te resulte útil!

photo credit: ecstaticistcc

José Manuel Alarcón José Manuel Alarcón
Fundador de campusMVP.es, el proyecto de referencia en formación on-line para programadores en lengua española. Autor de varios libros y cientos de artículos. Galardonado como MVP de Microsoft desde 2004. Gallego de Vigo, amante de la ciencia y la tecnología, la música y la lectura. Ayudando a la gente en Internet desde 1996.
Descarga GRATIS mi último libro (no técnico): "Tres Monos, Diez Minutos".
Banner

Comentarios (9) -

Spain Eduardo Ozores Baltar

muy util
se aplicaria muchisimas veces si lo pusiesen facil los ms access o similar

Responder

Muy bueno, simple y práctico,... y un caso de uso más frecuente de lo que parece.
Gracias Jose.

Responder

Colombia Oscar Gallego

Excelente aporte, muchísimas gracias no sólo lo lei sino que ya lo utilice.

Responder

Muy buen aporte, esto es lo que se llama nadie sabe para quien trabaja...muy buen uso del FOR XML PATH.

Responder

Genial, muy bueno y muy útil también para otros propósitos.
Gracias .

Responder

Mexico Edy Badal

Excelente material.... Muy bien explicado... Me sirvio mucho...
Gracias

Responder

Mejora para formato JSON:

SELECT CompanyName ,
'['  +   (
             SELECT  '{ID:' + CHAR(39) + CAST(OrderID AS nvarchar(20)) + CHAR(39)  + ', DATE:'  + CHAR(39) +
             CAST(OrderDate AS NVARCHAR(20)) + CHAR(39) + '}, ' FROM Orders
             WHERE CustomerID = Customers.CustomerID
             FOR XML PATH('')
         ) + ']' AS Pedidos
FROM Customers
ORDER BY CompanyName ASC

Responder

Gracias Fue Muy Útil.

Responder

Consulta:

En Sql Server, tengo una variable @TextoXML XML exportada desde vb.net con 40 o más registros y multiples campos que algunos de ellos los creo siguiendo un patrón lógico para su nombre.

Ahora, en Sql Server necesito, mediante programación recuperar el nombre del campo (en este instante desconozco el nombre del campo).

Es factible que se tengo solución?

Por un momento pienso, en las tablas con schema puede hacerse, pero para xml, se puede?

Responder

Agregar comentario