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

En SQL, una tabla dinámica es un conjunto de datos que se transforma de una colección de filas separadas a una colección de columnas. En las bases de datos relacionales, como Microsoft SQL Server, Oracle y MySQL, las tablas pivote se pueden utilizar para simplificar la gran cantidad de datos con el fin de que sea más fácil de leer y entender. Para crear una tabla dinámica, un agregado se utiliza en contra de un conjunto de datos para distribuir varias filas de una sola columna en una sola fila con varias columnas. Esta gira esencialmente el conjunto de resultados de lado.

Data de muestra

Para entender mejor una tabla dinámica, un ejemplo de algunos datos de ventas está aquí. Copiar el siguiente en Microsoft SQL Server Management Studio para probar los ejemplos.

Crear una tabla #PivotTestTable
(Varchar CustName (8),
ITEM_TYPE varchar (8),
Item_Amount numérico (6,2))

insertar en #PivotTestTable
seleccione 'Jason', 'PC', 435.34
Unión
seleccione 'Jason', 'software', 243.54
Unión
seleccione 'Jason', 'Monitor', 158.23
Unión
seleccione 'Alison', 'PC', 345.89
Unión
seleccione 'Alison', 'software', 78.78
Unión
seleccione 'Alison', 'Monitor', 123.45

A partir de datos no girados

Cuando se consulta la tabla temporal, #PivotTestTable, el resultado es el siguiente.

CustName item_type Item_Amount

Como se puede ver, el conjunto de resultados muestra dos clientes, Alison y Jason, que han adquirido tres diferentes tipos de artículos. Hay seis filas de datos para dos clientes. Si lo que queríamos ver los datos en una sola fila por cliente, usaríamos una tabla dinámica para conseguir el resultado deseado.

Pivote por función de pivote

Microsoft SQL Server tiene una función de pivote integrado en SQL Server. Aquí hay un ejemplo con los datos #PivotTestTable.

SELECCIONAR

CustName as Total_Sales_By_Cust,
Computer,
Monitor,
Software

DE

(
SELECT
CustName,
Item_Type,
Item_Amount
FROM #PivotTestTable
) a

PIVOTE

(
sum(Item_Amount)
FOR Item_Type in (Computer, Monitor,Software)
) b

Esta consulta devolverá los originales seis filas articuladas en dos filas con columnas separadas para cada tipo de artículo vendido. El conjunto de resultados generados a partir de esta consulta es aquí:

Software Total_Sales_By_Cust Monitor

Pivote de estado global del caso

Mediante el uso de una función agregada (SUM, AVG, MIN, MAX) en torno a una declaración de caso en una consulta SQL, somos capaces de lograr el mismo resultado que la función de pivote con menos trabajo.

SELECCIONAR

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

DE #PivotTestTable
GRUPO POR CustName

Esta consulta devolverá exactamente el mismo conjunto de resultados del ejemplo anterior y es sólo una preferencia por el tipo de pivote para su uso.

El error común con las tablas dinámicas

Un error común para crear una tabla dinámica es crear una copia de combinación en la tabla de origen. Esto producirá resultados poco fiables y debe ser evitado. Este ejemplo es estrictamente un ejemplo de lo que no debe hacer. El resultado en esta muestra será el mismo; sin embargo, este ejemplo no funcionará en todos los casos.

SELECCIONAR

p1.CustName,
p1.Item_Amount as Computer,
p2.Item_Amount as Monitor,
p3.Item_Amount as Software

De P1 #PivotTestTable
Combinación interna p2 #PivotTestTable

on p1.CustName = p2.CustName
and p2.Item_Type = 'Monitor'

Combinación interna p3 #PivotTestTable

on p1.CustName = p3.CustName
and p3.Item_Type = 'Software'

DONDE p1.Item_Type = 'PC'