Este es el último post sobre las novedades que presenta en lenguaje T-SQL para la versión 2008 de SQL Server.
Instrucción PIVOT
La instrucción PIVOT nos permitirá obtener un conjunto de resultados en un formato similar a una tabla dinámica de Excel, facilitando su análisis para la posterior toma de decisiones. Veamos un ejemplo, en el que obtendremos las ventas de productos por comercial, zona y año:
SELECT soh.[SalesPersonID]
,c.[FirstName] + ' ' + COALESCE(c.[MiddleName], '') + ' '
+ c.[LastName] AS [FullName]
,e.[Title]
,st.[Name] AS [SalesTerritory]
,soh.[SubTotal]
,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear]
FROM [Sales].[SalesPerson] sp
INNER JOIN
[Sales].[SalesOrderHeader] soh
ON sp.[SalesPersonID] = soh.[SalesPersonID]
INNER JOIN
[Sales].[SalesTerritory] st
ON sp.[TerritoryID] = st.[TerritoryID]
INNER JOIN
[HumanResources].[Employee] e
ON soh.[SalesPersonID] = e.[EmployeeID]
INNER JOIN
[Person].[Contact] c
ON e.[ContactID] = c.ContactID
En el resultado obtenido se muestran los datos deseados, pero… ¿Y si quisiéramos mostrar los ventas totales por comercial y año? Una forma rápida de resolverlo sería mediante el uso de la instrucción PIVOT, tal y como se muestra seguidamente:
SELECT * FROM
(SELECT
soh.[SalesPersonID]
,c.[FirstName] + ' ' + COALESCE(c.[MiddleName], '') + ' '
+ c.[LastName] AS [FullName]
,e.[Title]
,st.[Name] AS [SalesTerritory]
,soh.[SubTotal]
,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear]
FROM [Sales].[SalesPerson] sp
INNER JOIN [Sales].[SalesOrderHeader] soh
ON sp.[SalesPersonID] = soh.[SalesPersonID]
INNER JOIN [Sales].[SalesTerritory] st
ON sp.[TerritoryID] = st.[TerritoryID]
INNER JOIN [HumanResources].[Employee] e
ON soh.[SalesPersonID] = e.[EmployeeID]
INNER JOIN [Person].[Contact] c
ON e.[ContactID] = c.ContactID
) AS source
PIVOT
(
SUM([SubTotal])
FOR [FiscalYear]
IN ([2002], [2003], [2004])
) as pvt
La transformación ha permitido poner en columnas los valores de los distintos años (2002, 2003 y 2004), y en las filas el comercial y su zona, mientras que en el área de valores hemos puesto las ventas. Cabe observar que aparecen valores NULL debido a que no todos los comerciales han realizado ventas durante alguno de los años.
Instrucción UNPIVOT
Mediante UNPIVOT podremos hacer el paso inverso al mostrado previamente. A partir de unos resultados mostrados en formato agrupado, podemos intentar mostrarlos en formato tabla. Para clarificar el ejemplo, en primer lugar guardaremos el resultado de la instrucción PIVOT (mostrado anteriormente) en una tabla (se llamará dbo.VentasComercial):
SELECT *
INTO dbo.VentasComercial
FROM
(SELECT
soh.[SalesPersonID]
,c.[FirstName] + ' ' + COALESCE(c.[MiddleName], '') + ' '
+ c.[LastName] AS [FullName]
,e.[Title]
,st.[Name] AS [SalesTerritory]
,soh.[SubTotal]
,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear]
FROM [Sales].[SalesPerson] sp
INNER JOIN [Sales].[SalesOrderHeader] soh
ON sp.[SalesPersonID] = soh.[SalesPersonID]
INNER JOIN [Sales].[SalesTerritory] st
ON sp.[TerritoryID] = st.[TerritoryID]
INNER JOIN [HumanResources].[Employee] e
ON soh.[SalesPersonID] = e.[EmployeeID]
INNER JOIN [Person].[Contact] c
ON e.[ContactID] = c.ContactID
) AS source
PIVOT
(
SUM([SubTotal])
FOR [FiscalYear]
IN ([2002], [2003], [2004])
) as pvt
Ahora podemos ejecutar la instrucción UNPIVOT, accediendo a la información almacenada en la tabla dbo.VentasComercial.
SELECT SalesPersonID,
Title,
SalesTerritory,
FiscalYear,
Sales
FROM
(SELECT SalesPersonID,
FullName,
Title,
SalesTerritory,
[2002],
[2003],
[2004]
FROM dbo.VentasComercial) as pvt
UNPIVOT (Sales FOR FiscalYear IN ([2002],[2003],[2004])) AS unpvt;
Finalmente podemos observar como tenemos resultados similares al primer conjunto de resultados mostrado en este artículo, aunque en este caso sólo obtenemos un registro por comercial y año.
Funciones RANK y DENSE_RANK
Entre las distintas funciones de ranking que presenta SQL Server, vamos a ver en primer lugar la función DENSE_RANK. Mediante esta instrucción podemos obtener en qué posición se encuentra una fila respecto al conjunto total de resultados. En el ejemplo que se muestra seguidamente se realiza un ranking de productos según la cantidad que existe de cada uno de ellos en los distintos almacenes:
SELECT i.ProductID,
p.Name,
i.LocationID,
i.Quantity
,DENSE_RANK() OVER
(PARTITION BY i.LocationID ORDER BY i.Quantity) AS 'RANK'
FROM Production.ProductInventory i
INNER JOIN
Production.Product p
ON i.ProductID = p.ProductID
ORDER BY i.LocationID,i.Quantity
Es importante destacar que se pueden producir empates, cuando existe la misma cantidad de productos distintos en un mismo almacén.
Así, también disponemos de la función RANK, que es similar a la anterior, aunque en este caso los números asignados en el ranking no son contiguos.
SELECT i.ProductID,
p.Name,
i.LocationID,
i.Quantity
,RANK() OVER
(PARTITION BY i.LocationID ORDER BY i.Quantity) AS 'RANK'
FROM Production.ProductInventory i
INNER JOIN
Production.Product p
ON i.ProductID = p.ProductID
ORDER BY i.LocationID,i.Quantity
En este caso, como se produce un empate en el ranking 7, el siguiente numero asignado es el 9, hecho que no se producia con la función DENSE_RANK.
Funciones de Fecha y Hora
En la versión de SQL Server 2008 han aparecido nuevos tipos de datos para gestionar fechas y horas: datetime2, time, date y datetimeoffset , que permiten almacenar y manipular fechas en un formato más preciso que el tipo datetime. En el siguiente ejemplo vamos a mostrar como usar los distintos tipos de datos y las funciones SYSUTCDATETIME, SYSDATETIME y SYSDATETIMEOFFSET:
SELECT SYSUTCDATETIME() AS UTC,
SYSDATETIME() AS FECHA_HORA,
SYSDATETIMEOFFSET() AS DIF_HORARIA,
CONVERT(DATE,SYSDATETIME()) AS FECHA,
CONVERT(TIME,SYSDATETIME()) AS HORA
Podemos comprobar como podemos convertir un dato del tipo datetime2 (el que nos devuelve la función SYSDATETIME) a un tipo date o time para obtener unicamente la fecha o la hora.
Aunque existen más novedades, explicaré algunas de ellas en artículos más concretos.
Espero que todo lo que he contado os sirva… ¡Y enjoy T-SQL!