¿Qué es una tabla dinámica en SQL?

Por aj graham

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