¿Qué es una tabla dinámica en SQL?
En SQL, una tabla dinámica es un conjunto de datos que se transforma a partir de un grupo de filas separadas para un grupo de columnas. En bases de datos relacionales, como Microsoft SQL Server, Oracle y MySQL, las tablas dinámicas pueden utilizarse para simplificar datos extensos con el fin de que sea más sencillo leerlos y entenderlos. Para crear una tabla dinámica, se utiliza un agregado contra un conjunto de datos para distribuir varias filas de una sola columna en una sola fila con varias columnas. Esto dinamiza esencialmente el conjunto de resultados lateralmente.
Datos de muestra
Para entender mejor una tabla dinámica, a continuación se muestra un ejemplo de algunos datos de ventas. Copia lo siguiente en Microsoft SQL Server Management Studio para probar los ejemplos.
Create table #PivotTestTable (CustName varchar(8), Item_Type varchar(8), Item_Amount numeric(6,2))
insert into #PivotTestTable select 'Jason', 'Computer', 435.34 union select 'Jason', 'Software', 243.54 union select 'Jason', 'Monitor', 158.23 union select 'Alison', 'Computer', 345.89 union select 'Alison', 'Software', 78.78 union select 'Alison', 'Monitor', 123.45
Empezando datos no dinámicos
Cuando la tabla temporal, # PivotTestTable, es consultada, el resultado es el siguiente.
CustName Item_Type Item_Amount
Alison Computer 345.89 Alison Monitor 123.45 Alison Software 78.78 Jason Computer 435.34 Jason Monitor 158.23 Jason Software 243.54
Como puedes ver, el conjunto de resultados muestra dos clientes, Alison y Jason, compraron tres tipos diferentes de productos. Hay seis filas de datos para dos clientes. Si queremos ver los datos en una sola fila por cada cliente se utiliza una tabla dinámica para lograr el resultado deseado.
Función dinámica por DINAMICA
Microsoft SQL Server tiene una función dinámica incorporada en SQL Server. Aquí hay un ejemplo con los datos de # PivotTestTable.
SELECT CustName as Total_Sales_By_Cust, Computer, Monitor, Software FROM (
SELECT CustName, Item_Type, Item_Amount FROM #PivotTestTable ) a PIVOT ( sum(Item_Amount) FOR Item_Type in (Computer, Monitor,Software) ) b
Esta consulta devolverá las seis filas originales articuladas en dos filas con columnas separadas para cada tipo de artículo vendido. El conjunto de resultados generados a partir de esta consulta está abajo.
Total_Sales_By_Cust Computer Monitor Software
Alison 345.89 123.45 78.78 Jason 435.34 158.23 243.54
Dinámica por declaración de caso acumulado
Usando una función acumulativa (SUM, AVG, MIN, MAX) en torno a una declaración de caso en una consulta SQL, podemos lograr el mismo resultado que la función dinámica con menos trabajo.
SELECT CustName as Total_Sales_By_Cust, sum(case Item_Type when 'Computer' then Item_Amount end) as Computer, sum(case Item_Type when 'Monitor' then Item_Amount end) as Monitor, sum(case Item_Type when 'Software' then Item_Amount end) as Software FROM #PivotTestTable GROUP BY CustName
Esta consulta devolverá exactamente el mismo resultado del ejemplo anterior y es solamente una preferencia por el tipo de dinámica a utilizar.
Errores comunes con tablas dinámicas
Un error común para crear una tabla dinámica consiste en crear una combinación nueva en la tabla de origen. Esto producirá resultados poco fiables y debería evitarse. Este ejemplo es estrictamente una muestra de lo qué no hay que hacer. El resultado será el mismo, sin embargo este ejemplo no funcionará en todos los casos.
SELECT p1.CustName, p1.Item_Amount as Computer, p2.Item_Amount as Monitor, p3.Item_Amount as Software FROM #PivotTestTable p1 INNER JOIN #PivotTestTable p2 on p1.CustName = p2.CustName and p2.Item_Type = 'Monitor' INNER JOIN #PivotTestTable p3 on p1.CustName = p3.CustName and p3.Item_Type = 'Software' WHERE p1.Item_Type = 'Computer'
Referencias
