Geeks•ms
Todo lo que los geeks de Windows y .Net tienen que contar
Wave Engine: Useful. Simple. Free.
[Code] Pivote dinámico con sql server

MVP

Código QR

Syndication

MCS Specialist

Red de Conocimiento

Pivotes Dinámicos

En muchas ocaciones tenemos información en filas y queremos convertirlas en columnas, ésto se presenta especialmente en escenarios de análisis en donde tener la información en colunas es un requerimiento.

Revisemos el ejemplo que presenta MSDN en su documentación

Si tenemos una consulta:

USE AdventureWorks2008R2 ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost 
FROM Production.Product
GROUP BY DaysToManufacture;
 

La cual arroja los siguiente resultados

DaysToManufacture AverageCost
0 5.0885
1 223.88
2 359.1082
4 949.4105

y queremos verla en columnas

Cost_Sorted_By_Production_Days 0 1 2 3 4
AverageCost 5.0885 223.88 359.1082 NULL 949.4105

Debemos usar PIVOT

 

SELECT 'AverageCost' AS
Cost_Sorted_By_Production_Days, [0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost FROM Production.Product) AS SourceTable
PIVOT (AVG(StandardCost) FOR DaysToManufacture IN ([0], [1], [2], [3], [4]) ) AS PivotTable;

Revisando la sintaxis de la consulta tenemos que debemos definir una a una las columnas que deseamos obtener con la consulta y que éstas correspondan al Valor que queremos poner en esa columna, es decir, para cero (0) días de manufactura debemos crear una columan con el nombre 0 para que se asocie el valor que se tiene para esa columna, y se observa también que se debe incluir una función de agregación para crear un contexto para cada celda en el momento de poner el valor en la columna, esto es clave para poder generar el pivote.

Si se ha trabajado anteriormente con tablas dinámicas se verá que el comportamiento es similar tan solo debemos escribir nosotros mismos la consulta

EN SQL 2000

Acá tenemos una idea de cómo se podría solucionar en Sql 2000, algunas de las funciones que se presentan en ésta solución pronto será descontinuadas en las futuras versiones de SQL

use AdventureWorks2008R2;
go

SELECT  
  [No. Sales per year]=CASE WHEN row IS NULL THEN 'Sum'  
                   ELSE CONVERT(VARCHAR(80),[row]) END , 
  [1996] =SUM( CASE col WHEN '2005' THEN data ELSE 0 END ), 
  [1997] =SUM( CASE col WHEN '2006' THEN data ELSE 0 END ), 
  [1998] =SUM( CASE col WHEN '2007' THEN data ELSE 0 END ), 
  [Total]= SUM( data ) 
FROM  
   (SELECT [row]=P.firstname+' '+ P.lastname,  
           [col]=YEAR(SOH.OrderDate),  
           [data]=COUNT(*) 
    FROM Sales.SalesPerson SP INNER JOIN Sales.SalesOrderHeader SOH ON (SP.BusinessEntityID=SOH.SalesPersonID)  
    inner join HumanResources.Employee E ON SP.BusinessEntityID = E.BusinessEntityID 
    inner join Person.Person P ON E.BusinessEntityID = P.BusinessEntityID 
    GROUP BY P.firstname+' '+ P.lastname, YEAR(SOH.OrderDate) 
    )f 
GROUP BY row WITH ROLLUP 
ORDER BY GROUPING(row),total DESC

 

IDEAS

Siempre que aprendemos algo se nos ocurren mas ideas, tales como: Qué pasa si se tienen días de manufactura diferentes? qué pasa si quiero una columna para cada uno de esos días, que pasa si lo hacemos por producto? por pais? qué pasa si cada uno de ellos tiene un comportamiento diferente?

Muchas de esas preguntas se responden realizando un pivote con columnas dinámicas, es decir, al contrario de la sintaxis normal en donde debemos definir cada columna, que se interprete y de obtengan todas las columnas que necesito sin necesidad de definirlas una a una

CONSULTA DINÁMICA

 

use AdventureWorks2008R2;
go

declare @columnas varchar(max)

set @columnas = ''

select @columnas =  coalesce(@columnas + '[' + cast(DaysToManufacture as varchar(12)) + '],', '')
FROM (select distinct DaysToManufacture from Production.Product) as DTM

set @columnas = left(@columnas,LEN(@columnas)-1)

DECLARE @SQLString nvarchar(500);

set @SQLString = N'
SELECT *
FROM
(SELECT DaysToManufacture, StandardCost 
    FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN (' + @columnas + ')
) AS PivotTable;'

EXECUTE sp_executesql @SQLString

Pero esta solución tiene el inconveniente de tener que lidiar con consultas Ad-Hoc (Dinámicas) que asocian posibles problemas de seguridad y rendimiento a la solución, esta solución así como la demás que se puedan implementar debe considerar dónde se van a usar, posiblemente sea mas eficiente realizar el pivote en reporting services (Matriz) que en Sql, esto debe evaluarse para cada y depende de lo que se quiera realizar

OPCION CTE - Nombres de columna dinámicos

Para 2005 o superior existe una alternativa para facilitar, mas no soluciona del todo el problema y es crear una consulta que asigna valores (números a las columnas) facilitando la construcción final de la consulta. En este caso queremos pivotear por el nombre del mes, pero supongamos que el rango que damos en la primera consulta cambia, entonces los nombre de las columnas (Nombre del mes) no podrían ser usados.

Por ejemplo si enviamos como parámetro el primero de agosto,  hasta el 31 de diciembre o si enviamos el 1 de enero al 30 de junio, tendríamos que cambiar constantemente la consulta o presentar todos los meses, para solucionar esto tenemos el siguiente ejemplo

use AdventureWorksDW2008R2;
go

;With monthyears as (
SELECT DISTINCT SpanishMonthName as CalendarMonthYearName, MonthNumberOfYear as MonthOfYear,CalendarYear
      FROM dbo.DimDate 
      WHERE DateKey > @FechaInicio AND DateKey < @FechaFinal
),
CalMonthYears AS
(
SELECT CalendarMonthYearName AS 'CalendarMonthYearName',
      MonthOfYear,
      CalendarYear,
      ROW_NUMBER() OVER (ORDER BY CalendarYear DESC) AS 'YearMonthNum'  
      FROM monthyears
)
SELECT ProductKey,
      [1],[2],[3],[4],[5],[6]
FROM
(
-- your query to get your data, pivot this data
SELECT YearMonthNum,s.ProductKey,
      SUM(OrderQuantity) AS 'Quantity'
      FROM FactResellerSales s
      INNER JOIN dbo.DimDate d ON s.OrderDateKey = d.DateKey
      INNER JOIN CalMonthYears cmy ON d.SpanishMonthName = cmy.CalendarMonthYearName
GROUP BY YearMonthNum,s.ProductKey
) AS SourceTable
PIVOT
(
SUM(Quantity)
FOR YearMonthNum IN (
      [1],[2],[3],[4],[5],[6]
      )
) AS PivotTable

 

Espero sea de ayuda, 

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP


Enviado 2/1/2012 10:02 por Freddy Angarita
Comparte este post:

Comentarios

MVP Factor escrito Agenda MVP (del 9 al 15 de Enero 2012)
en 9/1/2012 19:05

Artículo ComboBox using ExtJS and ASP.NET MVC By John Charles Olamendy Dynamics CRM COMException: The

Sigue a Plain Concepts en Facebook