Me he topado en varias ocasiones con la necesidad de crear una vista en la que un campo contiene concatenados el valor de varios registros de otra tabla. Casi siempre he acabado recurriendo a Google para recordar cómo hacerlo, porque, por alguna razón que desconozco, esta información no queda registrada en mi hipocampo.

En este artículo vamos a ver cómo conseguirlo con un ejemplo muy sencillo. Partimos de 3 tablas: autor, libro y libro_autor, que contienen, como podemos suponer, una relación de autores, libros y libro-autor. La estructura de estas tres tablas es la que se muestra en el siguiente diagrama.

Ahora supongamos que queremos mostrar el listado de todos los libros y que en una columna queremos que aparezcan concatenados el nombre de todos los autores del libro. El resultado que queremos obtener es el siguiente:

La forma más sencilla de obtener este resultado es creando una función que devuelva el nombre de todos los autores, pasando como parámetro el identificador del libro. Esto lo podemos hacer fácilmente haciendo uso de las funciones ISNULL o COALESCE. En este ejemplo, lo conseguimos mediante la función COALESCE.

CREATE FUNCTION dbo.DevuelveActores ( @id\_libro int )
RETURNS VARCHAR(40)
AS
BEGIN
   DECLARE @autores VARCHAR(40)

   SELECT @autores = COALESCE(@autores + ', ' + nombre, nombre)
   FROM libro\_autor la
   LEFT JOIN autor a ON la.id\_autor = a.id\_autor
   WHERE id\_libro=@id\_libro

   RETURN @autores
END

Ahora podemos, mediante la siguiente consulta, obtener el resultado deseado:

SELECT id_libro, titulo, autores = dbo.DevuelveActores(id_libro)
FROM libro

Otra opción es hacer uso del modo PATH de la cláusula FOR XML. Este modo permite definir, mediante los nombres o alias de la columnas, la manera cómo se asignan los valores en el XML, así como el nombre del elemento, pudiendo sobrescribir el valor __ predeterminado. Por ejemplo, la siguiente consulta devuelve una cadena con todos los autores concatenados.

SELECT nombre as \[data()\] FROM autor FOR XML PATH ('')

Haciendo uso de esta característica, es posible montar una consulta con una subconsulta que devuelva todos los autores de un libro y reemplazar el espacio entre autores por el separador que queramos. Con la siguiente consulta obtenemos exactamente el mismo resultado que hemos conseguido antes con la función.

SELECT id_libro, titulo, autores = CAST( REPLACE( (
   SELECT nombre as [data()]
   FROM libro_autor la
   LEFT JOIN autor a ON la.id_autor = a.id_autor
   WHERE id_libro = l.id_libro
   FOR XML PATH ('')
   ), ' ', ', ') AS NVARCHAR(40) )
FROM libro l

Dejo un enlace para descargar el script que contiene las consultas de los dos métodos explicados y la creación de todos los elementos de base de datos (tablas y funciones) que he utilizado en este ejemplo.