1 comentario

Operador Pivot y Unpivot



 Pivot y Unpivot en SQL Server 2005

Autor: Salvador Ramos
http://www.helpdna.net/

PIVOT y UNPIVOT es una de las novedades que nos proporciona Transact SQL 2005. Aquí tenéis un ejemplo de uso que os resultará muy familiar, sobre todo a los que ya habéis utilizado las Referencias cruzadas de Access.

Como convertir columnas en filas y viceversa?,  aqui les dejo este post que explica muy bien la utilizacion del operador que aparecio en la version 2005 de SQLServer, 

Se ha escuchando hablar bastante de la nueva sentencia PIVOT que viene con SQL Server 2005, ps bien, esta vez sólo quiero ejemplificar  acerca del tema. PIVOT es un sentencia que se usa para cruzar filas en columnas para mostrar resultados, la cual por cierto, toma un vison más estructurada, clara y agradable,… esta sentencia debe usarse para obtener resultados resumen más claros y descriptivos. Más información de esta sentencia pueden leerlo:  http://msdn2.microsoft.com/es-es/library/ms177410.aspx.

Bueno,
pues ahora vamos al tema.

 

Pivot y Unpivot en SQL Server 2005

Me he decidido a escribir sobre una de las novedades que nos proporciona el
lenguaje Transact SQL de SQL Server 2005, los operadores relacionales PIVOT y
UNPIVOT, que nos permitirán hacer cosas similares a las que se realizan en
Access con las Consultas de Referencias Cruzadas, y que todos echáis de
menos cuando migráis a SQL Server y necesitáis hacer algo similar.

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 de ellos y
teníamos que realizarlo mediante la función CASE.

 

Supongamos
que tenemos que mostrar una columna con las unidades vendidas de cada artículo
de nuestro almacén por mes del año. Este es un caso típico que muchas veces
hemos almacenado en tablas renormalizadas con acumulados. Aquí os muestro los
datos que queremos mostrar:

 

Año 1997

Ene

Feb

Mar

Abr

May

Jun

Jul

Ago

Sep

Oct

Nov

Dic

Art.1

Art.2

Art.3

Art.4

 

 

Para que
podáis probar el ejemplo vamos a utilizar la tan utilizada base de datos
Northwind (base de datos de ejemplo suministrada con SQL Server 7 y 2000).
Utilizaremos las tablas Orders, Order Details y Products (que traducidas serían
algo así como Cabecera de pedidos, Detalle de pedidos, y Productos). 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:

 

select P.ProductName as
Producto, month(O.OrderDate) as Mes, sum(D.Quantity) as Cantidad

from [Order Details]
D inner join
Orders O on D.OrderID
= O.OrderID

                       inner join Products P
on D.ProductID = P.ProductID

where year(O.OrderDate) = 1997

group by P.ProductName, month(O.OrderDate)

order by 1, 2

 

El resultado obtenido es:

 

Producto          Mes   Cantidad

Alice
Mutton      1     97

Alice
Mutton      2     10

Alice
Mutton      5     73

Alice
Mutton      6     34

Alice
Mutton      7     100

Alice
Mutton      9     30

Alice
Mutton      10    55

Alice
Mutton      11    20

Alice
Mutton      12    108

Aniseed
Syrup     1     50

Aniseed
Syrup     3     20

Aniseed
Syrup     5     60

 

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:

 

select P.ProductName as
Producto,

 sum(CASE WHEN month(O.OrderDate) = 1 THEN D.Quantity ELSE 0 END) AS Ene,

 sum(CASE WHEN month(O.OrderDate) = 2 THEN D.Quantity ELSE 0 END) AS Feb,

 sum(CASE WHEN month(O.OrderDate) = 3 THEN D.Quantity ELSE 0 END) AS Mar,

 sum(CASE WHEN month(O.OrderDate) = 4 THEN D.Quantity ELSE 0 END) AS Abr,

 sum(CASE WHEN month(O.OrderDate) = 5 THEN D.Quantity ELSE 0 END) AS May,

 sum(CASE WHEN month(O.OrderDate) = 6 THEN D.Quantity ELSE 0 END) AS Jun,

 sum(CASE WHEN month(O.OrderDate) = 7 THEN D.Quantity ELSE 0 END) AS Jul,

 sum(CASE WHEN month(O.OrderDate) = 8 THEN D.Quantity ELSE 0 END) AS Ago,

 sum(CASE WHEN month(O.OrderDate) = 9 THEN D.Quantity ELSE 0 END) AS Sep,

 sum(CASE WHEN month(O.OrderDate) = 10 THEN D.Quantity ELSE 0 END) AS Oct,

 sum(CASE WHEN month(O.OrderDate) = 11 THEN D.Quantity ELSE 0 END) AS Nov,

 sum(CASE WHEN month(O.OrderDate) = 12 THEN D.Quantity ELSE 0 END) AS Dic

from [Order Details]
D inner join
Orders O on D.OrderID
= O.OrderID


inner join
Products P on D.ProductID
= P.ProductID

where O.OrderDate between ‘19970101’ and ‘19971231’

group by P.ProductName

order by
1

 

El
resultado obtenido es:

 


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

Alice
Mutton 97  10   0    0  73  34  100   0   30  55  20 108

Aniseed
Syrup     50   0  20    0  60   0   14   0    0   6  20  20

Boston Crab Meat  52  20
61   30  35  52   42  60  144   0  99   1

Camembert
Pierrot 60   0  60  106  35  24  125  30  135  10  65  15

 

Como veis
no es algo tan trivial y flexible como en Access, pero con un poquito de
trabajo adicional si que se pueden realizar este tipo de consultas.

 

Si ya
disponemos de SQL Server 2005, entonces tendremos disponible una nueva
funcionalidad que nos permite realizar de forma más sencilla este tipo de
tareas, al ofrecernos los operadores relacionales PIVOT y UNPIVOT.

 

PIVOT nos
permite convertir filas en columnas. Lo podemos incluir en la cláusula FROM de
nuestras instrucciones SELECT.

 

select Producto, [1] Ene, [2] Feb, [3] Mar, [4] Abr, [5] May, [6] Jun,

       [7] Jul, [8] Ago, [9] Sep, [10] Oct, [11] Nov, [12] Dic

from (

    — select inicial, a
pivotar. Podría ser una tabla

   select P.ProductName
as Producto, month(O.OrderDate) as Mes, 

          D.Quantity as Cantidad

   from
[Order Details] D inner join Orders O on D.OrderID = O.OrderID

          inner join Products P
on D.ProductID = P.ProductID

      where O.OrderDate between ‘19970101’ and ‘19971231’

     ) V PIVOT (
sum(Cantidad) FOR Mes IN ([1], [2], [3], [4], [5],

                 [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. De UNPIVOT poco, más bien nada he hablado, simplemente quiero citar
que hace justo lo contrario. Si nos encontramos con la típica tabla de
acumulados, con una columna por cada mes podemos convertirla en varias filas,
una por cada mes.

 

Ya para finalizar,
quiero comentaros que, a parte de la función PIVOT, que en ciertas ocasiones se
puede quedar corta para las tareas que deseáis realizar, y que podéis echar en
falta algunas de las características de las referencias cruzadas de
Access, en SQL Server 2005, tenéis todo un motor multidimensional con servicios
OLAP, llamado Analysis Services 2005. No pretendo en este momento presentar
aquí un producto tan amplio y potente como los Analysis Services, sino que
simplemente quiero dejar una referencia. Y si alguien quiere comenzar a ampliar
información, puede comenzar por la sección que tengo dedicada a este tema en mi
sitio (www.helpdna.net), concretamente
en la sección dedicada a este tema:

http://www.helpdna.net/sqlserver_olap_datawarehouse_analysis_services.htm

FUENTE: http://www.elguille.info/NET/ADONET/firmas_salva_Pivot_Unpivot.htm

Anuncios

Un comentario el “Operador Pivot y Unpivot

  1. Muy buena la información, me sirvió para entender la sentencia pivot

Si te sirvio o te gusto el post, deja un comentario, o vota por el blog, esto nos ayuda a seguir creciendo, Gracias

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: