jueves, 26 de septiembre de 2013

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...

6 comentarios:

  1. Interesante la clausula CROSS y el operador APPLY. Eran totalmente desconocidas para mi. Gracias por hacerme aprender cosas nuevas.

    De todas formas yo abogo por tener un 'modelo de vista' que represente un cliente que contenga sus datos personales y una lista de coches en vez de hacer proyecciones de los datos formateados desde un procedimiento almacenado, ya que si en otra vista se necesita que aparezcan los coches separados por comas se necesitaria otro procedimiento almacenado que haga lo mismo. Simplemente recuperar este modelo del sistema de persistencia y despues solo habria que renderizarlo en HTML (o a cualquier otra tecnologia de vistas que queramos)

    Table1
    %for each cliente in ListaClientes%
    TR1
    TD1 %Cliente.Nombre%
    TD2 %Cliente.Apellido%
    TD3 %=Cliente.ListaCoches.ToViewString('/')'% //parametro con el caracter separador de coches
    /TR1
    %end for%
    /Table1

    Esta sintaxis de enlace es generica pero os la podeis imaginar en PHP o en ASP.NET por ejemplo.

    ResponderEliminar
  2. Estoy de acuerdo contigo, no comparto la filosofía (por llamarlo de alguna forma) de tener todo el negocio en procedimientos de bases de datos (cosa que sufrimos con desesperante frecuencia). Pero no me negarás que para tareas eventuales de explotación queda bastante chulo ¿no?

    ResponderEliminar
    Respuestas
    1. En ese caso, todo es relativo dependiendo de lo que puedes poder realizar en tu área de desenvuelvo.

      Existen factores que realmente es necesario o partiría como prioritario llevar el modelo vista desde el tratamiento de datos, a no ser que existen cálculos dentro de ORDER's con GROUP y que puedes realizarlo en tu Grid o código del Deployment para lo más óptimo.

      Puedes hacer un SP que una de las variables sea el separador, y no tendrás que tocar DB al momento de cambiar algo en el cliente.

      Incluso! Más práctico que cambiar algo en el fuente de interfaz que tendrás nuevamente que compilar y reemplazar, entre otras alternativas que no podré ni procesar..... Cuando de DB se refiere.

      Mientras menos tengas que cambiar tu deployment por detalles, mejor. Y nada mejor que tratar con datos que desde su origen.

      Saludos.

      Eliminar
  3. Claro que no! Seguro que conociendo estas instrucciones alguna vez se tercia su uso. :)

    ResponderEliminar
  4. Meses después... Tiene huevos lo que nos complicamos a veces la vida. Por algún avatar del destino me he tenido que enfrentar recientemente otra consulta de este tipo -contatenar todos los resultados de una columna en un solo campo de una fila- y voy y me doy cuenta de una cosa, esta tontada de script hace exactamente lo mismo que toda la mandanga del CROSS APPLY (que probablemente tiene usos mucho más dignos):

    SELECT Nombre
    ,Apellido
    ,(SELECT Modelo + ' / '
    FROM VEHICULOS_CLIENTES vc
    WHERE c.CLiente_Id = vc.Cliente_ID
    FOR XML PATH ('') ) 'Modelos'
    FROM CLIENTES c

    Lo único determinante en esta consulta es la cláusula FOR XML que es la que determina el formato de salida de la cadena de caracteres.... ¬_¬U

    ResponderEliminar
    Respuestas
    1. Probablemente sea uno más que haya leído tu respuesta actualizada luego de un año.

      Casualmente, he requerido un tratamiento en consulta idéntica a la mostrada que te tenía en marcador desde hace 6 meses, y sabía que algún día te iba agradecer oficialmente jeje!.

      En mi caso, nunca me pidieron este resultado, pero lo quiero dar práctico y bien estructurado, dado que seguro su visualización estaría mejor debido que evito repetir las filas por los recursos en fila que no poseen importancia ni relación, solo información.

      Ese FOR XML tengo que estudiarlo bien. Varias veces me ha salvado la vida, desde llevar formato HTML al correo desde SQL hasta esto.

      Agradecido por compartir tu experiencia T-SQL ;).

      Eliminar