Post original en JASoft.org: http://www.jasoft.org/Blog/post/Como-devolver-los-resultados-de-una-sub-consulta-como-un-unico-campo-de-texto.aspx
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:
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:
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:
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:
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:
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.
Hands on SQL Server 2012: Expert Programming and Design (en inglés)
¡Espero que te resulte útil!
photo credit: ecstaticist cc
En 
