Creación de informes de referencias cruzadas. El Operador PIVOT.

A veces es necesario girar resultados de modo que, los datos en columnas se presentan en sentido horizontal y los datos en filas se presentan en forma vertical. Esto se conoce como la creación de una tabla dinámica ®, creando un informe de referencias cruzadas, o rotación de datos.
En primer lugar vamos a comenzar poniendo un ejemplo de cómo se debía realizar algo similar, aunque con mucha menos potencia en SQL Server 2000, en el que no disponíamos del operador PIVOT y donde teníamos que realizarlo mediante la función CASE.

Supongamos que disponemos de una estructura típica maestro detalle con las tablas Orders, Order Details y deseamos un informe similar al siguiente:

Ventas por año

  Ene Feb Mar Abr May Jun Jul Ago Sep Oct Nov Dic
2000                        
2001                        
2002                        
...                        

La información que queremos mostrar, ya agrupada, la podemos obtener con la siguiente instrucción SELECT, aunque no en el formato que la necesitamos mostrar:

  1. select year(O.OrderDate), month(O.OrderDate) as Mes, sum(D.Quantity) as Cantidad
  2. from [Order Details] D inner join Orders O on D.OrderID = O.OrderID
  3. where year(O.OrderDate) > 2000
  4. group by P.ProductName, month(O.OrderDate)
  5. order by 1, 2

El resultado obtenido es:

 

Producto Mes Cantidad
2000 1 97
2000 2 10
2000 3 73
2000 4 34
.... .. ..
2001 1 55

Ahora, si queremos mostrarlo según el formato que hemos definido anteriormente, y estamos utilizando SQL Server 2000, tenemos que recurrir a la función CASE, como se muestra a continuación:

  1. select year(O.OrderDate),
  2. sum(CASE WHEN month(O.OrderDate) = 1 THEN D.Quantity ELSE 0 END) AS Ene,
  3. sum(CASE WHEN month(O.OrderDate) = 2 THEN D.Quantity ELSE 0 END) AS Feb,
  4. sum(CASE WHEN month(O.OrderDate) = 3 THEN D.Quantity ELSE 0 END) AS Mar,
  5. sum(CASE WHEN month(O.OrderDate) = 4 THEN D.Quantity ELSE 0 END) AS Abr,
  6. sum(CASE WHEN month(O.OrderDate) = 5 THEN D.Quantity ELSE 0 END) AS May,
  7. sum(CASE WHEN month(O.OrderDate) = 6 THEN D.Quantity ELSE 0 END) AS Jun,
  8. sum(CASE WHEN month(O.OrderDate) = 7 THEN D.Quantity ELSE 0 END) AS Jul,
  9. sum(CASE WHEN month(O.OrderDate) = 8 THEN D.Quantity ELSE 0 END) AS Ago,
  10. sum(CASE WHEN month(O.OrderDate) = 9 THEN D.Quantity ELSE 0 END) AS Sep,
  11. sum(CASE WHEN month(O.OrderDate) = 10 THEN D.Quantity ELSE 0 END) AS Oct,
  12. sum(CASE WHEN month(O.OrderDate) = 11 THEN D.Quantity ELSE 0 END) AS Nov,
  13. sum(CASE WHEN month(O.OrderDate) = 12 THEN D.Quantity ELSE 0 END) AS Dic
  14. from [Order Details] D inner join Orders O on D.OrderID = O.OrderID
  15. where O.OrderDate between '20000101' and '20101231'
  16. group by year(O.OrderDate)
  17. order by 1

El resultado obtenido es:

                 Ene Feb Mar  Abr May Jun  Jul Ago  Sep Oct Nov Dic

2000              97  10   0    0  73  34  100   0   30  55  20 108

2001              50   0  20    0  60   0   14   0    0   6  20  20

2002              52  20  61   30  35  52   42  60  144   0  99   1

2003              60   0  60  106  35  24  125  30  135  10  65  15

...

 

Si ya disponemos de SQL Server 2005 o posteriores, entonces tendremos disponible una nueva funcionalidad que nos permite realizar de forma más sencilla este tipo de tareas. Esta funcionalidad nos la ofrece el operador PIVOT (en otra ocasión hablaremos del operador UNPIVOT). PIVOT nos permite convertir filas en columnas. Lo podemos incluir en la cláusula FROM de nuestras instrucciones SELECT.

  1. select anio, [1] Ene, [2] Feb, [3] Mar, [4] Abr, [5] May, [6] Jun,
  2. [7] Jul, [8] Ago, [9] Sep, [10] Oct, [11] Nov, [12] Dic
  3. from (
  4. -- select inicial, a pivotar. Podría ser una tabla
  5. select year(O.OrderDate) as anio, month(O.OrderDate) as Mes,
  6. D.Quantity as Cantidad
  7. from [Order Details] D inner join Orders O on D.OrderID = O.OrderID
  8. where O.OrderDate between '20000101' and '20101231'
  9. ) V PIVOT ( sum(Cantidad) FOR Mes IN ([1], [2], [3], [4], [5],
  10. [6], [7], [8], [9], [10], [11], [12]) ) as PT

Como podéis comprobar, simplemente tenemos que usar la función PIVOT, a la cual le indicamos una función de agregado, la columna que queremos girar (pivotar) y desglosamos las columnas en las que queremos mostrar la información, esto nos permite utilizar ese desglose realizado como cualquier otra columna más en nuestra instrucción SELECT. La función PIVOT nos permite girar (pivotar), convirtiendo los valores únicos de una columna en varias columnas de salida, y realizando agregaciones para dichos valores.

Comentarios

Convertir de fila a columna

yo tengo una tabla que se llama history y tiene 9 campos el cual hay un campo que se llama FKey dentro de ello tiene (Inicio, Inicio de almuerzo, fin de almuerzo, salida) que son datos que se llena diario, el cual es el registro diario de la persona (asistencia), ahora yo quiero que mi reporte cuando lo genere con un query me salga sde esa forma. campo 1 campo 2 campo 3 campo4 inicio inicio de almuerzo fin de almuerzo salida

Enviar un comentario nuevo

El contenido de este campo se mantiene privado y no se mostrará públicamente.
  • Las direcciones de las páginas web y las de correo se convierten en enlaces automáticamente.
  • Etiquetas HTML permitidas: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Saltos automáticos de líneas y de párrafos.
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <pre>, <c>, <cpp>, <csharp>, <css>, <drupal5>, <drupal6>, <html>, <java>, <javascript>, <jquery>, <mysql>, <php>, <python>, <robots>, <ruby>, <sql>, <tsql>, <vb>, <vbnet>, <xml>. The supported tag styles are: <foo>, [foo]. PHP source code can also be enclosed in <?php ... ?> or <% ... %>.

Más información sobre opciones de formato

CAPTCHA
Esta pregunta se hace para comprobar que es usted una persona real e impedir el envío automatizado de mensajes basura.
CAPTCHA de imagen
Escriba los caracteres que se muestran en la imagen.