Tablas Pivote en tus querys con MSSQL

Que es una tabla pivote?

En el procesamiento de datos, una tabla pivote es una herarmienta de sumarizacion y visualizacion de datos. Se puede encontrar en herramientas de “Inteligencia de Negocios”. Tambien pueden buscar los terminos “tabulacion cruzada”. Si quieren pensarlo en una forma simple, imaginense reordenar una tabla, donde las columnas se convierten en lineas y las lineas se agrupan(sumatoria, average, etc) para poder desplegarse.

Por que hacemos esto? Bueno datos son datos y solo sirven de algo si tienen algun sentido para alguien, por lo tanto tu trabajo es ordenar esos datos de la manera que tenga mas sentido para el usario, que le sea mas facil asimilar la informacion.

No quiero adentrarme mucho por que problablemente ya conozcan los terminos, pero de no ser asi, siempre tenemos a wikipedia:

Definiciones Wikipedia aqui y en ingles aqui

Requisitos

Para seguir este ejemplo necesitaran MS SQL por supuesto, pueden usar la version express. Les dejo el link a MS-SQL aqui si no lo tienen. Tambien les dejo el link a la base de datos de AdventureWorks, buscaremos por ahi algo que podamos usar para el ejemplo.

MS-SQL AdventureWorks Base de datos de ejemplo

La expresion PIVOT

Iniciemos con la sintaxis de PIVOT, la obtuve de MSDN y le traduje los comentarios.

SELECT <non-pivoted column>,
    [primera columna pivoteada] AS <column name>,
    [segunda columna pivoteada] AS <column name>,
    ...
    [ultima columna pivoteada] AS <column name>
FROM
    (<SELECT query that produces the data>)
    AS <alias for the source query>
PIVOT
(
    <aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
    IN ( [first pivoted column], [second pivoted column],
    ... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;

Sobre los datos

Para Nuestro ejemplo utilizaremos solo dos tablas:

[AdventureWorks2012].[Sales].[SalesOrderHeader] [AdventureWorks2012].[Person].[Person]

Revisas la estructura y encuentras:

SELECT COLUMN_NAME, DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = 'Person' 
	AND TABLE_NAME = 'Person'

SELECT COLUMN_NAME, DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = 'Sales' 
	AND TABLE_NAME = 'SalesOrderHeader'

Un Ejemplo

Imaginense esto, estas tranquilamente tomando cafe cuando se te hacerca el gerente de ventas y te comenta que quiere conocer cuanto venden sus vendedores anualmente, te quedas pensa, para que carajos quiere esto y pues bueno como no te da trabajo le dice que si que con gusto le envias las ventas. Vas a tu escritorio y haces algo como esto (Y si lo haces en produccion te traes abajo el servidor)

SELECT P.FirstName + ' ' + P.LastName AS [Vendedor], 
		year(OrderDate) AS 'year', 
		SUM([TotalDue]) as [TotalVentas]
FROM	[AdventureWorks2012].[Sales].[SalesOrderHeader] Sales 
		INNER JOIN [AdventureWorks2012].Person.[Person] P 
		ON P.BusinessEntityID = Sales.SalesPersonID
GROUP BY P.FirstName + ' ' + P.LastName, 
		year(OrderDate)

Le envias los dato y te responde, si esa es la informacion que ocupaba muchas pero … mem podria acomodar las ventas totales en columnas, es que ocupamos comparar el rendimiento de los vendedores. Y bueno hasta ahi te llega la sonrisa. Inicialmente podrias tener dos opciones:

Tomar un cursor y ordenar los datos en una forma manual usando una tabla temporal con las columnas. O crear un query con la sumarizacion de cada columna y luego unirlos todos. Cualquiera que sea la opcion es bastante costosa en terminos de recursos y hace que un reporte que en teoria no deberia se dificil de obtener se vuelva una tarea campal.

Por suerte ya podemos expresar esta tabla pivote en una forma mas rapida y menos costosa.

Noten que la seleccion inicial esta anidada dentro de otra que simplemente lista todo. Esto se debe a que de otra manera al revisar tu query el compilador no encuentra las columnas compuestas. Yo tengo varias, primero para obtener el nombre del vendedor y segundo para mostrar el mes de la venta. No tienen que estar sumadas. PIVOT se encarga de definir las columnas a mostrar, que son daos que tienen que estar en una delas columanas y a sumarizar. El resto de las columnas que se utilizen seran las primeras columans en esta tabla pivote.

SELECT * FROM (
	SELECT	P.FirstName + ' ' + P.LastName AS [Vendedor], 
			year(OrderDate) AS 'year', 
			[TotalDue] as [TotalVenta]
	FROM	[AdventureWorks2012].[Sales].[SalesOrderHeader] Sales 
			INNER JOIN [AdventureWorks2012].[Person].[Person] P 
			ON P.BusinessEntityID = Sales.SalesPersonID
 ) AS Ventas
PIVOT (
	SUM (TotalVenta)
	FOR [year] in ([2005], [2006], [2007], [2008])
	) as [MontoVentasAnuales]

El resultado:

Ejemplo en MSDN Otro Ejemplo

Bueno ya saben. Experimentando se aprende!