Cómo concatenar subconsultas en un solo campo con SQL Server
Menú de navegaciónMenú
Categorías

La mejor forma de Aprender Programación online y en español www.campusmvp.es

Cómo concatenar subconsultas en un solo campo con SQL Server

Vamos a ver con un ejemplo de qué modo podemos solucionar una situación común que se da en bases de datos.

Imaginemos que tenemos dos tablas relacionadas entre sí. La primera contiene datos de productos (su nombre, descripción, precio, etc...). La segunda es una tabla de categorías a las que pueden pertenecer los productos. Como un producto puede estar en más de una categoría a la vez, existe una tercera tabla que relaciona a las dos anteriores.

El ejemplo, por supuesto, podría ser con cualquier otro tipo de tablas y datos, pero con este tan sencillo ilustraremos bien nuestro caso.

Para visualizarlo mejor vamos a pintar una representación sencilla de estas tres tablas y sus relaciones:

Relaciones entre las tablas

Y estos son algunos datos de prueba en ellas:

Datos de prueba en las tablas

Como vemos, algunos productos están en más de una categoría. Por ejemplo, el yogur está en lácteos y postres, y la leche está en esos dos y además en bebidas.

Podemos obtener un listado con todos los productos y sus categorías usando una consulta de tipo INNER JOIN entre ambas tablas, por ejemplo esta:

SELECT Productos.Nombre, Categorias.Categoria
FROM Categorias INNER JOIN
  ProductosCategorias ON Categorias.idCategoria = ProductosCategorias.idCategoria INNER JOIN
  Productos ON ProductosCategorias.idProducto = Productos.idProducto

Lo cual nos devuelve el siguiente listado:

Nombre Categoria
Queso Lacteos
Queso Postres
Pan Alimentos básicos
Yogur Lacteos
Yogur Postres
Leche Bebidas
Leche Lacteos
Leche Alimentos básicos


Donde vemos cada producto y sus categorías.

El problema de esta consulta es que es un producto cartesiano, por lo tanto nos devuelve un registro por cada categoría del producto, es decir, si un producto está en tres categorías tenemos tres registros para el mismo producto como resultado de la consulta (mira por ejemplo la leche al final).

Por supuesto podríamos efectuar una agrupación y contar a cuántas categorías pertenece cada producto, pero si queremos ver cuáles son específicamente, entonces necesitamos tener varias veces el mismo producto en los resultados.

Pero ¿qué pasa si necesitamos tener en un solo campo todas las categorías de cada producto agrupadas y listas para usar?...

Es decir, algo como esto:

Nombre Categorias
Queso Lacteos, Postres
Pan Alimentos básicos
Yogur Lacteos, Postres
Leche Bebidas, Lacteos, Alimentos básicos


Donde solo tenemos una fila por cada producto, y un nuevo campo ficticio llamado Categorias que contiene todas las categorías separadas por una simple coma.

Vamos a ver cómo conseguirlo paso a paso...

1.- Subconsulta para obtener las categorías

Lo primero que vamos a ver es cómo podemos obtener, para un producto dado, todas las categorías a las que este pertenece. En realidad ya sabemos hacerlo a partir de la consulta anterior, quedándonos solo con la parte de las tablas de categorías y la intermedia, y metiendo un filtro por el identificador del producto, así:

SELECT Categorias.Categoria
FROM Categorias
INNER JOIN ProductosCategorias ON ProductosCategorias.idCategoria = Categorias.idCategoria
WHERE ProductosCategorias.idProducto = 4

En este caso obtenemos todas las categorías del producto 4, o sea, la leche. Nos devuelve tres líneas de resultados, una por cada categoría.

Pero ¿cómo podemos hacer para que en lugar de 3 líneas como ahora, nos devuelva todas las categorías concatenadas en una sola cadena de texto?

2.- La conversión a XML que nunca lo fue

Desde SQL Server 2005 existe una característica integrada en su lenguaje de consultas T-SQL que sirve para obtener resultados en formato XML. Se trata de la cláusula FOR XML.

Ésta permite obtener un único campo como respuesta de una consulta que contiene una representación en XML de los datos devueltos por ésta. Por ejemplo, si a la consulta anterior le añadimos un FOR XML AUTO, así:

SELECT Categorias.Categoria
FROM Categorias
INNER JOIN ProductosCategorias ON ProductosCategorias.idCategoria = Categorias.idCategoria
WHERE ProductosCategorias.idProducto = 4
FOR XML AUTO

Obtendremos este resultado:

<Categorias Categoria="Bebidas"/>
<Categorias Categoria="Lacteos"/>
<Categorias Categoria="Alimentos básicos"/>

Como vemos devuelve los registros con el nombre de la tabla como nodo XML y un atributo con el nombre del campo para el resultado.

Esta cláusula tiene diversas variantes que cambian la forma del XML devuelto. Una de ellas, la que nos interesa, es FOR XML PATH que permite especificar qué elemento queremos usar para rodear a los resultados anteriores. Es decir, el nodo raíz. Además, tiene otra particularidad: si un campo que devolvamos no tiene nombre, lo devuelve tal cual, como un valor dentro del XML, ya que no tiene forma de ponerle un atributo o un nodo para rodearlo (FOR XML AUTO no permite campos sin nombre en el resultado).

¿Cómo conseguimos un campo sin nombre? Pues simplemente cualquier campo calculado al que no le asignemos uno con AS, será sin nombre. Así, basta por ejemplo con concatenar una cadena vacía a un campo para que deje de tener nombre:

Campo sin nombre

Bien. Entonces, teniendo en cuenta estas dos cosas, ¿cómo conseguimos concatenar el valor del campo, por ejemplo, separándolo con comas?

Pues usando un campo sin nombre y no rodeando al XML con nodo alguno, así:

SELECT ', ' + Categorias.Categoria
FROM Categorias
INNER JOIN ProductosCategorias ON ProductosCategorias.idCategoria = Categorias.idCategoria
WHERE ProductosCategorias.idProducto = 4
FOR XML PATH ('')

Que nos devuelve un resultado XML que en realidad no es XML ya que obtenemos:

, Bebidas, Lacteos, Alimentos básicos

Como vemos esto es casi lo que queríamos: le sobra la coma inicial.

3.- Librarnos de la coma inicial

Vale. Ya tenemos casi lo que necesitamos, pero nos sobra esa coma del principio. Por suerte es muy fácil librarse de ella gracias a una función específica de T-SQL denominada STUFF.

Esta función inserta una cadena dentro de otra. Elimina una longitud determinada de caracteres de la primera cadena a partir de la posición de inicio y, a continuación, inserta la segunda cadena en la primera, en la posición de inicio.

Es decir, en una cadena, a partir de la posición que le digamos, elimina los caracteres que le indiquemos y luego mete en ese lugar la cadena que queramos. Es como quitar e insertar por el medio, o sea, rellenar (que es el significado de la palabra stuff en inglés).

Por ejemplo, esta expresión:

SELECT STUFF('Pericardio', 5, 5, 'pl')

lo que hace es ir a la posición 5 (la "c" de "cardio"), eliminar 5 caracteres (o sea, "cardi"), y luego meter entre esa posición y lo que reste la cadena "pl". Con ello el resultado que obtenemos es la palabra:

Periplo

que aprovecha la "o" final.

En el caso que nos ocupa, por tanto, eliminar la coma y el espacio en blanco inicial es muy sencillo: se eliminan los dos primeros caracteres y se "inserta" una cadena vacía, así:

STUFF(Cadena, 1, 2, '')

Que si lo llevamos a la consulta anterior que devolvía las categorías en una cadena, es:

SELECT STUFF(
    (SELECT ', ' + Categorias.Categoria
    FROM Categorias
    INNER JOIN ProductosCategorias ON ProductosCategorias.idCategoria = Categorias.idCategoria
    WHERE ProductosCategorias.idProducto = 4
    FOR XML PATH ('')),
1,2, '')

Obteniendo:

Bebidas, Lacteos, Alimentos básicos

Ya sin esa coma y espacio en blanco iniciales.

4.- Ahora todo junto

Ahora que ya sabemos cómo obtener los datos concatenados en una cadena solo resta mezclar eso con la consulta original, en la que queríamos obtener las categorías de cada producto. Esto es muy fácil de conseguir ahora simplemente metiendo la consulta que acabamos de crear como sub-consulta de la principal y asignándole un nombre. La cosa quedaría así:

SELECT Productos.Nombre, 
 STUFF(
    (SELECT ', '  + Categorias.Categoria
    FROM Categorias
    INNER JOIN ProductosCategorias ON ProductosCategorias.idCategoria = Categorias.idCategoria
    WHERE ProductosCategorias.idProducto = Productos.idProducto
    FOR XML PATH('')),
    1, 2, '') As Categorias
FROM Productos

Como vemos, es una simple consulta que devuelve dos campos, siendo el segundo de ellos una sub-consulta que genera la lista de categorías de cada producto a partir de su identificador (ahora en el WHERE en lugar de poner un valor constante usamos el valor del campo idProducto de la consulta principal).

Con esto conseguimos exactamente lo que necesitábamos y veremos este resultado:

Nombre Categorias
Queso Lacteos, Postres
Pan Alimentos básicos
Yogur Lacteos, Postres
Leche Bebidas, Lacteos, Alimentos básicos


¡Listo!

En conclusión

Esta técnica tiene multitud de aplicaciones prácticas, que van más allá del ejemplo que acabamos de hacer.

En el rendimiento no tiene demasiado impacto, salvo quizá si concatenamos enormes cantidades de datos para obtener una gran cadena.

Si observamos el plan de ejecución de la primera consulta que hace el producto cartesiano:

Plan ejecución consulta inicial

Y el plan de ejecución de la consulta final:

Plan ejecución consulta final

Vemos que son muy parecidos, y además se sabe que la concatenación con FOR XML PATH tiene un impacto muy pequeño pues está muy optimizada por el motor de datos.

Una cosa importante a tener en cuenta: si el contenido de los textos que concatenamos tiene caracteres propios de etiquetas HTML o XML, básicamente símbolos de menor y mayor (< o >) y el "et" o "ampersand" (&), éstos se devolverán codificados como HTML, es decir, como &lt;, &gt; y &amp;. El motivo es que lo que se está devolviendo es XML (aunque no lo parezca) y los datos deben ir correctamente codificados. Esto de hecho puede ser muy útil si estamos en una aplicación web, ya que no tendremos ni que codificarlos para mostrarlos. Pero si puede suponer un problema debemos tenerlo en cuenta en la aplicación que haga uso de los datos devueltos y volver a codificarlos con los caracteres iniciales.

Si lo deseas te puedes bajar (ZIP, 3KB) la pequeña base de datos de ejemplo que he utilizado para este artículo, junto con un archivo con las diferentes consultas utilizadas de modo que puedas probarlo con más facilidad.

¡Espero que te resulte útil!

José Manuel Alarcón Director de campusMVP, es ingeniero industrial y especialista en consultoría de empresa. Ha escrito diversos libros, habiendo publicado hasta la fecha cientos de artículos sobre informática e ingeniería en publicaciones especializadas. Puedes seguirlo en Twitter en @jm_alarcon o leer sus blog técnico o personal. Ver todos los posts de José Manuel Alarcón

No te pierdas ningún post

Únete gratis a nuestro canal en Telegram y te avisaremos en el momento en el que publiquemos uno nuevo.

Archivado en: Acceso a Datos

Comentarios (3) -

SistemasPeru
SistemasPeru

Buen aporte, felicitaciones.

Responder

José Manuel Alarcón
José Manuel Alarcón

Gracias :-)

Responder

Beatus Ego
Beatus Ego

Buenas noches,

Un gran truco, sin lugar a dudas, quiero fácilmente puede extenderse y generalizarse para en definitiva "aplanar árboles en listas".

Por cierto, como curiosidad, indicar que las capacidades de XML aparecieron en MSSQL2000 e incluso tuvieron por aquel entonces un componente "en el lado del cliente" denominado SQLXML y todo un XML SDK para aquella versión.

Un abrazo.

Responder

Agregar comentario