Una útil combinación de CROSS APPLY y FOR XML

De los numerosos problemas que pueden darse a la hora de presentar información de una base de datos a un cliente, quizás uno de los más recurrentes sea que quieran ver determinados datos agrupados -concatenados- en una única columna. Por ejemplo, supongamos que hay un concesionario de coches de lujo que sirve vehículos exclusivos a una serie de clientes. El caso es que el concesionario mantiene una tabla de clientes y una tabla de vehículos asociados a los clientes. Como son clientes con mucha pasta, cada uno puede tener un número indeterminado de coches (ya que pensamos un ejemplo no vamos a andarnos con miseras de supermercados ecológicos donde se compran calabacines y tomatitos cherry...). 

En la versión más elemental (no necesito más para este ejemplo) las tablas tendrían este aspecto: 

Tabla: CLIENTES

Cliente_ID	Apellido	Nombre
C001	Ortega	Bill
C002	Buffet	Amancio
C003	Gates	Warren

Tabla: VEHICULOS_CLIENTES

Vehiculo_ID	Cliente_ID	Modelo
V001	C001	Bugatti Veyron Super Sport
V002	C001	Ferrari 599XX
V003	C002	Zenvo ST1
V004	C003	Koenigsegg Agera R
V005	C003	Aston Martin One-77
V006	C002	Maybach Landaulet
V007	C001	Pagani Huayra
V008	C001	Hennessey Venom GT
V009	C002	SSC Tuatara
V010	C002	Porsche 918 Spyder

Por simplicidad supondré que todos los coches son distintos.

Y ahora es cuando llegaría nuestro particular CLC (Chief Listings Collector) y nos pediría un listado de clientes con todos sus coches, PERO cada cliente debe aparecer una única vez, con todos sus coches junto al nombre, separados por comas (o barras o lo que sea...)

He visto varias formas de resolver consultas de este tipo en Transact SQL, con tablas temporales y estructuras iterativas, con cursores... Pero la forma que expongo aquí me parece muy elegante y eficiente (si trabajamos con SQL Server 2005 o posterior). Voy a poner el código del script y luego me explico un poco:

SELECT client.Nombre, client.Apellido, cars.Modelos
FROM (
 SELECT Cliente_ID, Nombre, Apellido 
 FROM CLIENTES
     ) AS client
CROSS APPLY(
        SELECT Modelo + ' / ' AS '*'
        FROM VEHICULOS_CLIENTES
        WHERE Cliente_ID = client.Cliente_ID
        FOR XML PATH('')
     ) AS cars(Modelos)

Primero algunas generalidades:

El operador “APPLY” permite invocar una función con valores de tabla para cada fila devuelta por una expresión de tabla externa de una consulta. En otras palabras -y tomando como referencia nuestro script- para cada fila de la primera SELECT se genera una tabla de resultados en la SELECT dentro del paréntesis del APPLY, y se combinan en la salida final. El código dentro del APPLY es una función que devuelve una tabla (de hecho toda esa SELECT se puede sacar del script a una FUNCTION como veremos). Existen dos formas de APPLY: CROSS APPLY y OUTER APPLY. Cuando usamos CROSS APPLY sólo se devuelven las filas de la tabla exterior que producen un conjunto de resultados en la función interior. Si usamos OUTER APPLY se devuelven todas las filas de la tabla exterior, por lo que pueden aparecer valores NULL en la columna o columnas producidas por la función interior. En este caso estamos usando CROSS APPLY para una tarea sencilla de concatenación, pero puede intuirse que se trata de un operador con muchas y potentes posibilidades.

Como he dicho, el código dentro de CROSS APPLY puede meterse en una función de forma parecida a esto:

CREATE FUNCTION dbo.obtenerCoches(@cliente AS VARCHAR(4))
RETURN TABLE
AS 
RETURN
SELECT Modelo + ' / ' AS '*' 
FROM VEHICULOS_CLIENTES
WHERE Cliente_ID = @cliente
GO

Luego podríamos invocar esa función en nuestra consulta:

SELECT client.Nombre, client.Apellido, Modelos.[*]
FROM  (
 SELECT Cliente_ID, Nombre, Apellido 
 FROM #CLIENTES
      ) AS client
CROSS APPLY dbo.obtenerCoches(Cliente_ID) AS Modelos FOR XML PATH('')

Otro detalle interesante es el uso de la cláusula FOR XML. En general esta cláusula permite recuperar los resultados de una consulta como código XML. Se permiten diferentes modos en el uso de FOR XML: RAW, AUTO, EXPLICIT y PATH. Como no es el objeto de esta entrada, baste decir que RAW genera un único elemento <row> para cada fila de las devueltas por la consulta. AUTO genera anidamiento en XML mediante mecanismos heurísticos (que dependen de cómo esté especificada la consulta. EXPLICIT permite un mayor control sobre la forma del XML, aunque escribir consultas en este modo puede ser relativamente complejo. Finalmente el modo PATH nos permite definir el nombre de las etiquetas XML con mayor facilidad y, combinado con la posibilidad de usar consultas anidadas FOR XML, puede ser una buena alternativa a EXPLICIT.

En el caso que nos ocupa, al definir en el modo PATH la cadena vacía, estamos especificando que los resultados de la consulta no deben llevar ninguna etiqueta XML. El resultado final de nuestro script sería algo así.

Nombre	Apellido	Modelos
Bill	Ortega	Bugatti Veyron Super Sport / Ferrari 599XX / Pagani Huayra / Hennessey Venom GT /
Amancio	Buffet	Zenvo ST1 / Maybach Landaulet / SSC Tuatara / Porsche 918 Spyder /
Warren	Gates	Gates Koenigsegg Agera R / Aston Martin One-77 /

Así que aquí queda, para futuras referencias. Esta es otra de esas píldoras de conocimiento que he usado un par de veces y que me gustaría tener a mano la próxima vez que mi “CLC” me llame por teléfono...