Menú de navegaciónMenú
Categorías

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

?id=3bd5670c-b075-4cd0-b8ab-693d695d1d6f

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.

Actualización 29/Dic/2017: Un par de cosas más, sugeridas por Pablo Doval (¡Gracias Pablo!):

  • Es mejor no utilizar esta técnica dentro de una función definida por el usuario (UDF) pues provoca una dramática pérdida de rendimiento en ese caso.
  • Si utilizas SQL Server 2017 o posterior, ahora hay soporte nativo del lenguaje T-SQL para hacer lo mismo a través de la función STRING_AGG, que es de uso directo para conseguir lo mismo.

 

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!

Fecha de publicación:
José Manuel Alarcón Fundador 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. Microsoft lo ha reconocido como MVP (Most Valuable Professional) en desarrollo web desde el año 2004 hasta la actualidad. Puedes seguirlo en Twitter en @jm_alarcon o leer sus blog técnico o personal. Ver todos los posts de José Manuel Alarcón
Archivado en: Acceso a Datos

¿Te ha gustado este post?
Pues espera a ver nuestra newsletter...

Suscríbete a la newsletter

La mejor formación online para desarrolladores como tú

Comentarios (28) -

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

Roberto Campos
Roberto Campos

Hola,
Muy buen aporte.
¿En qué versión de SQL Server está funcionando?
Un saludo

Responder

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

Hola Roberto:

Como comento en el artículo, a partir de SQL Server 2005 debería funcionarte.

Saludos.

Responder

hola,

deseo realizar este proceso pero tengo las datos en una sola tabla pero unen todos los campos y no los agrupa

me podrías ayudar?

Responder

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

Hola:

Sería exactamente igual. De hecho con el INNER JOIN lo único que haces es equivalente a "crear en memoria" una tabla única en la que están todos los datos de ambas tablas. Es decir, es equivalente a tener una sola tabla, que es tu caso. No deberías tener problema para realizar lo mismo. Deberías hacer la subconsulta apropiada nada más, que dependerá de tus datos.

Saludos

Responder

Maestro, muchas gracias justo lo que necesitaba!!!

Responder

GRAN APORTE!!!!!!111

Responder

puedes hacer con el GroupConcat y te libras de hacer tanta lineas de código confusas.

Responder

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

Eso que dices es para MySQL. Este artículo es para SQL Server, que no tiene esa función incorporada.

Responder

Gracias por la explicación, me ha servido de mucho.

Responder

Hola amigo, muchas gracias Hermano por la explicación, muy completa y satisfactoria, podrías hacer un post con la misma explicación, pero para aquellos que usan MySQL por favor, muchas gracias amigo

Responder

stuff es demasiado lento, lo mejor es crear una funcion y crear un cursor que concatene.

Responder

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

Hola Bernabé:

¿No sabes eso que dicen que, de que cada vez que usas un cursor, Dios mata un gatito?

Pues eso: los cursores son lentos y aunque son más fáciles de utilizar porque te ayudan a pensar en elementos individuales (registros) y no en tablas, casi siempre hay alternativa y suele ser mejor que usar cursores. Los buenos programadores suelen trabajar pensando que, de entrada, los cursores no existen.

En nuestro seminario de Optimización de consultas de SQL Server puedes ahondar en esta y otras muchas cuestiones: www.campusmvp.es/.../...ces-en-SQL-Server_214.aspx

Saludos!

Responder

Emmanuel Arroyo Rivera
Emmanuel Arroyo Rivera

En mi caso tengo varios datos repetidos, por ejemplo : Digamos que tuviera dos "Queso con categoría lácteos"
Como puedo eliminar los repetidos en la cadena concatenada Que pase de : "Queso: Lácteos, Lácteos, Postre" a simplemente "Queso: Lácteos, Postre"

Responder

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

Hola:

En el SELECT del primer paso utiliza DISTINCT y listo. No tiene más misterio.

Saludos

Responder

Hola, me funciona perfecto, pero quiero hacer un espacio de linea entre cada uno y mostrarlo en C#, como puedo hacerlo?

Responder

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

Hola:

Si quieres concatenarlos con un retorno de carro en lugar de con una coma solo tienes que sustituir ', ' en las consultas por CHAR(13) + CHAR(10) que son los caracteres para nueva línea y retorno de carro (en Windows se suelen usar los dos).

De todos modos esto quizá te fuese mejor hacerlo directamente en C# sustituyendo la coma y el espacio de la cadena final por el cambio de línea que mejor corresponda a donde lo quieres mostrar. Por ejemplo,  en la web seguramente querrías sustituirlo por un <br> y en windows por un "\n".

Saludos.

Responder

Miguel Angel Olivares
Miguel Angel Olivares

Primero que nada agradecerle por compartir sus conocimientos con nosotros, vengo siguiendo los artículos publicados sobre "Fundamentos de SQL" y justamente en uno de ellos se menciona la existencia de un libro gratuito que no pude encontrar =(, recurrí al buscador que tiene en su sitio web, pero aparentemente ya no está disponible =(, si usted pudiera, por favor, enviarme una copia al correo [email protected], realmente se lo agradecería.

Agradeciéndole nuevamente por el apoyo brindado, me despido.

Atentamente,

Miguel Angel
La Paz - Bolivia

Responder

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

Hola:

Ese libro ya no está disponible, pero tienes en el blog todos los artículos de la serie, que luego constituyeron el libro.

Saludos

Responder

hola, excelente aporte, aprovecho en realizar una pregunta a ver si me puedes ayudar.
Tengo el mismo problema un art pertenece a varias categorias por lo cual si realizo un select codart,  cat from cat_prod where codart='0001'  me da como resultado dos o mas registros dependiendo de las categorias que se encuente.

tengo esa tabla "cat_prod" MAL categorizada y tengo una tabla2 "tabla2.cat_prod" BIEN categorizada

deseo realizar  una verificación de categorías distintas a la tabla BUENA para poder hacer luego un update. y corregir la tabla mala.
espero haberme expresado bien y obtener algún consejo

Saludos.

Responder

Chechoman
Chechoman

Hola!
Tengo esta tabla:

CATEGORIA | NOMBRE     | PERTENECE_A
Animal         | Gato             | Tierra
Animal         | Perro            | Tierra
Animal         | Pez                | Mar
Animal         | Delfín           | Mar
Planta          | Orquídea     | Tierra
Planta          | Menta          | Tierra
Planta          | Orquídeas   | Aire
Planta          | Helechos   | Aire



y deseo un select que imprima esto:

PERTENECE_A  | JSON
Tierra       | '{"Animal":["Gato","Perro"],"Planta":["Orquídea","Menta"]}'
Mar          | '{"Animal":["Pez","Delfín"],"Planta":[]}'

Será posible con la técnica que explicas en este post?

Responder

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

Hola:

Podrías utilizar algo así para crear exactamente esto. Solo es seguir lo que explico en el post. Sin embargo no es necesario ya que SQL Server soporta la generación automática de JSON desde consultas de modo parecido a FOR XML, usando la cláusula FOR JSON:

docs.microsoft.com/.../json-data-sql-server

por lo que lo puedes hacer automáticamente.

Saludos.

Responder

Para cargar el dato en VB60 (sí, vb60) no se puede usar el XML PATH ya que el RECORDSET devuelve un grupo de registros tipo byte que quizás se pueda parsear a string.

Responder

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

No veo por qué no se iba a poder usar en VB6 ya que lo que devolvería el recordset es en cualquier caso una cadena y para el lenguaje que consume la consulta es indiferente lo que se utilice por debajo para obtener los resultados. Quizá lo que te esté ocurriendo es que tu aplicación con VB6 esté utilizando una versión de SQL Server anterior a la 2005, que es la que empezó a soportar FOR XML, pero por otras causas debidas al lenguaje no creo que sea el problema.

Saludos.

Responder

Hola!

No sé como conseguir esto con una query:

En una tabla tengo

Tabla Actividades
ID, codigo, descripcion
1, 12, Descripción de 12
1, 15 Descripción de 15
1, 23, Descripción de 23
1, 31, Descripción de 31

en otra tabla tengo

TABLA Clientes
ID, Cliente, Actividades
1, Antonio, 12|23|31 --> Lista de Actividades separadas por PIPE

Necesito una query qu eme devuelva:

Cliente | Actividades
Antonio | 12 - Descripción 12, 23 - Descripcion 23, 31 - Descripción 31

Como hago para separar por pipes en la SLEECT y recoger, concatenar y juntar todos los datos en una salida?

Gracias!

Responder

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

Hola Marcos:

En este caso quizá deberías separar ese campo "raro", Actividades, a una tabla temporal, y luego utilizar lo explicado en el artículo con esa tabla.

Pero lo mejor, sin duda, es que te replantees el diseño de la base de datos para que los datos se almacenen con una estructura adecuada. Si tienes que guardar las actividades de cada cliente y éstas pueden ser múltiples no deberías crear un campo para eso, sino otra tabla relacionada "uno a muchos", con lo que todo sería mucho más eficiente y sencillo.

Saludos.

Responder

Agregar comentario

Los datos anteriores se utilizarán exclusivamente para permitirte hacer el comentario y, si lo seleccionas, notificarte de nuevos comentarios en este artículo, pero no se procesarán ni se utilizarán para ningún otro propósito. Lee nuestra política de privacidad.