Transponiendo tablas en T-SQL con la cláusula PIVOT.

¿Cuántas veces, buscando soluciones para algún problema -navegando por Internet- damos con un código que modificamos y logramos poner a funcionar, aunque no sepamos muy bien cómo? Algo así como "¡ey! pues esto funciona pero ¿Podría implementarlo desde cero ahora que he visto la solución?... Espera, ¿Por qué he puesto esto aquí?...". A mi no me pasa, pero conozco a un amigo... Bueno, venga, sí que me ha pasado alguna vez, por ejemplo con la cláusula PIVOT de T-SQL... He tenido que usarla un par veces (al menos), sin prestarle nunca mucha atención, así que en la siguiente ocasión volvía a perder demasiado tiempo buscando por la red soluciones a medias. Por lo tanto, como viene siendo habitual cuando escribo estas parrafadas, espero que esta entrada sirva para ayudarme a entenderlo del todo o -al menos- para encontrar una referencia más directa la próxima vez que lo necesite (y bueno, a lo mejor es útil para alguien más...).

Transponer datos de una tabla -o pivotar datos de una tabla- es básicamente cambiar datos de filas a columnas (o viceversa). Pero en qué mundo descabellado y cruel podría ser necesario hacer una cosa así, os preguntaréis...; bueno, existe un modelo relacional de almacén de datos que se usa en entornos muy cambiantes que añaden o eliminan columnas en las tablas a lo loco o entidades que registran un montón de atributos. Se llama modelo EAV (o Entity-Attribute-Value). Este diseño consiste esencialmente en almacenar los datos en un formato de "atributo-valor". Uno de los mayores problemas que presenta este modelo es la dificultad para visualizar y presentar los datos. Imaginemos un ejemplo muy tonto:

Tenemos una entidad "coche" para la que podemos querer registrar los siguientes atributos (no todos obligatorios):

1. Modelo

2. Cilindrada

3. Caballos

4. Color

5. Número de Velocidades

6. Número de airbags

7. Número de plazas

8. Número de ruedas (¬_¬U

...

Yo que sé..., atributos a cascoporro vamos...

Nos podemos hacer un script para montar esto en base de datos:

create table dbo.Car
(
    idCar int not null,
    Attribute varchar(100) not null,
    Value varchar(255) not null
);
insert into dbo.Car
values(1, 'Modelo', 'Opel Vectra'),
 (1, 'Cilindrada', '1500'),
 (1, 'Caballos', '100'),
 (1, 'Color', 'Rojo'),
 (1, 'Velocidades', '5'),
 (1, 'Airbags', '5'),
 (1, 'Plazas', '5')

insert into dbo.Car
values(2, 'Modelo', 'Citroen Xara'),
 (2, 'Cilindrada', '1600'),
 (2, 'Caballos', '110'),
 (2, 'Color', 'Azul'),
 (2, 'Velocidades', '6'),
 (2, 'Plazas', '5'),
 (2, 'Ruedas', '5')

insert into dbo.Car
values(3, 'Modelo', 'Nissan Micra'),
 (3, 'Cilindrada', '1100'),
 (3, 'Caballos', '90'),
 (3, 'Color', 'Verde'),
 (3, 'Velocidades', '5'),
 (3, 'Airbags', '5'),
 (3, 'Plazas', '5'),
 (3, 'Ruedas', '5')

Si consultamos los datos podemos observar que, con tan solo tres entidades, la legibilidad ya es un poco cuestionable...

Lo que de verdad queremos es una cosa así:

Mucho más chulo, dónde va a parar...

Para conseguir la segunda representación de los datos hemos hecho esto:

select idCar, [Modelo], [Cilindrada], [Caballos], [Color], [Velocidades], [Airbags], [Plazas], [Ruedas]
from(
    select idCar, Attribute, Value 
    from dbo.Car
) as source
pivot
(
    max(Value)
    for Attribute in ([Modelo], [Cilindrada], [Caballos], [Color], [Velocidades], [Airbags], [Plazas], [Ruedas])
) as pvt

Esto de aquí expresa la sintaxis básica de PIVOT. A partir de los resultados de la select anidada se conforman los encabezados de la select padre. Para ello, la cláusula PIVOT "gira" la columna de atributos "pivotando" sobre un punto de giro, que en este caso es la expresión max(value). Como "punto de giro" nos vale cualquier función de agregado de SQL, pero es necesario usar alguna. En nuestro caso, al usar max nos aseguramos de que PIVOT sólo devuelva la primera ocurrencia que encuentre de cada uno de los valores del atributo. Si tuviéramos que leer esta expresión sería algo como "Ey Pivot, coge una sola vez cada nombre que te encuentres de entre los posibles valores del Atributo y los pones como cabeceras en las columnas de una tabla... Y ya si eso me dejas también bien colocaditos los valores de esos atributos..."

Pero veamos otro ejemplo. Supongamos ahora una tabla en la que almacenamos información sobre agricultores, cultivos y superficies. Un agricultor puede tener muchos recintos con cultivos y superficies diferentes, así que en este sentido no hay limitaciones, desde el momento en que un agricultor aparece en la tabla por primera vez, puede aparecer otras 50 (o 500) veces, con diferentes cultivos y superficies, y en distintos momentos. Voy a simplificar mucho, porque esto se puede complicar hasta el infinito (creedme, sé de lo que hablo...). Pero antes de nada, construyamos nuestro anodino sistema...

create table dbo.Agricola
(
 IdAgricultor int not null,
 NombreAgricultor varchar(20) not null,
 Recinto int not null,  
 Cultivo varchar(20) not null,
 Superficie numeric(10,2) not null 
);

insert into dbo.Agricola
values(1, 'Wenceslao', 1, 'guindo', 3.5)
insert into dbo.Agricola
values(2, 'Eladio', 3, 'olivar', 2.3)
insert into dbo.Agricola
values(1, 'Wenceslao', 2, 'guindo', 1.5)
insert into dbo.Agricola
values(3, 'Fredesvindo', 5, 'alcornoque', 1.0)
insert into dbo.Agricola
values(1, 'Wenceslao', 4, 'olivar', 4.2)
insert into dbo.Agricola
values(2, 'Eladio', 6, 'limonero', 3.5)
insert into dbo.Agricola
values(3, 'Fredesvindo', 7, 'alcornoque', 2.5)

Ahora, si hacemos una select de la tabla vamos a obtener algo como esto:

Ok, ahora imagínate la misma tabla con... no sé... 18000 agricultores y 350 cultivos diferentes (y recuerda que cada agricultor podría aparecer con uno o más cultivos, con diferentes superficies y una o más veces)... Vaaale, por mucho pivote de columnas que hagamos, en una tabla con -por ejemplo- una columna por cada uno de los 350 tipos de cultivos tampoco vamos a ver una mierda... A ningún cliente se se ocurriría pedir semejante barbaridad en una excel ¿a que no?... Pero supongamos que sí; en ese caso desearíamos que nuestra excel kilométrica mostrase una tabla de referencias cruzadas, con un listado de los agricultores, una columna por cada cultivo y un sumatorio de superficies; algo como esto:

Para obtener esta representación nuestra consulta tendría la siguiente pinta:

select idAgricultor, NombreAgricultor, [guindo], [olivar], [alcornoque], [limonero]
from (
 select idAgricultor, NombreAgricultor, Cultivo, Superficie
 from dbo.Agricola
) as source
pivot
(
 sum(Superficie)
 for Cultivo in ([guindo], [olivar], [alcornoque], [limonero])
) as pvt
order by idAgricultor

Esto ilustra cómo en el PIVOT, podemos utilizar cualquier función de agregado, en este caso el sumatorio de superficies. Por su parte en el for especificamos el atributo cuyos valores queremos enviar a las cabeceras de las columnas de nuestra representación de datos...

... Vale, vale, ya sé lo que estáis pensando, que esto está muy bien cuando conocemos los posibles valores del atributo que queremos transponer, y sobre todo -y esto es lo más importante- el número de posibles valores de ese atributo es "manejable"... Pero no podéis quitaros de la cabeza al cliente que quiere imprimir su hoja excel en una sábana de dos metros para arroparse con ella en las crudas y solitarias noches de invierno...

Este es uno de los problemas de usar la cláusula PIVOT a pelo, requiere conocer de antemano los valores posibles del atributo que queremos pivotar, y además es deseable que la variedad de estos valores sea un número manejable. En un entorno de trabajo más realista nos encontramos con el mismo problema en los dos ejemplos que hemos puesto: el número de modelos diferentes de coche y la variedad de posibles cultivos puede llegar a ser muy elevado.

Afortunadamente hay una solución: El pivote dinámico... *¡TARAAAAA!*... Veámoslo en los ejemplos:

--Pivot dinámico

declare @attr varchar(max)
set @attr = ''

declare @sql varchar(max)
set @sql = 'select idCar, #attr#
  from
  ( select idCar, Attribute, Value 
       from dbo.Car
  ) as source 
  pivot
  (
       max(value)
       for Attribute in (#attr#)
  ) as pvt'

select @attr = @attr + '[' + v.Attribute + '],'
from 
(    
 select distinct Attribute 
 from dbo.Car
) as v

set @attr = substring(@attr, 0, len(@attr))
set @sql = replace(@sql, '#attr#', @attr)

exec(@sql)

¿Qué hacemos aquí? Pues básicamente nos construimos una cadena de caracteres con el código SQL que luego ejecutaremos. La gracia aquí es que construimos la select de forma dinámica: Sobre la variable @attr montamos una cadena con los distintos valores -sean los que sean- que tendrá el atributo Attribute. Después, en la cadena @sql que contiene la consulta, sustituiremos la marca #attr# -que habíamos colocado estratégicamente- por la cadena @attr (que ahora contiene la definición de las nuevas columnas)... ¡Ajá!

Y lo mismo para el ejemplo de los agricultores:

--Pivot dinámico

declare @attr varchar(max)
set @attr = ''

declare @sql varchar(max)
set @sql = 'select idAgricultor, NombreAgricultor, #attr#
  from
  (  
       select idAgricultor, NombreAgricultor, Cultivo, Superficie
   from dbo.Agricola
  ) as source 
  pivot
  (  
       sum(Superficie)
   for Cultivo in (#attr#)
  ) as pvt'

select @attr = @attr + '[' + v.Cultivo + '],'
from 
(    
 select distinct Cultivo from dbo.Agricola
) as v

set @attr = substring(@attr, 0, len(@attr))
set @sql = replace(@sql, '#attr#', @attr)

exec(@sql)

Sin embargo no quiero acabar la entrada sin hacer algún matiz. Este tipo de soluciones no son la panacea; el uso de SQL dinámico es vulnerable a ataques por inyección de código. Esto que hemos hecho aquí puede ser útil para satisfacer alguna petición delirante ocasional en un trabajillo de explotación, pero yo no pondría este código en un procedimiento almacenado. La operación PIVOT facilita, no obstante, otro tipo de solución; existe una cláusula XML que permite generar una salida reordenada de los datos como código XML, pero hoy ya me he extendido bastante, igual en otra ocasión...