CONSULTAS DE REFERENCIAS
CRUZADAS
INTEGRANTES ANGULO MENDEZ, Angelo LEVANO CASTILLA, Carlos PARDO FIGUEROA HERENCIA,
Jhonatan QUISPE ARCOS, Hans RAMIREZ GAMBOA, Marlon
CONSULTAS DE REFERENCIA CRUZADA
La sentencia TRANSFORM es la que se utiliza para definir una consulta de referencias cruzadas.
La sintaxis es la siguiente:
La sentencia SELECTEn la SELECT la columna fija es la columna que define el encabezado de filas, el origen que indicamos en la cláusula FROM es la tabla (o tablas) de donde sacamos la información, y en la cláusula GROUP BY ponemos la columna que va a definir las filas del resultado.La SELECT puede contener una cláusula WHERE para seleccionar la filas que se utilizan para calcular el resultado, puede contener subconsultas pero no la cláusula HAVING. La sentencia PIVOTEn la cláusula PIVOT indicamos la columna cuyos valores van a definir columnas dinámicas del resultado a esta columna la llamaremos pivote. La sentencia IN La cláusula IN permite definir el conjunto de valores que queremos que aparezcan como columnas dinámicas.
SELECT rep as empleado,month(fechap
edido)as mes,sum(importe)as
vendidoFROM
pedidosGROUPBY rep,month(fechapedido)
TRANSFORM Sum(importe)SELECT rep as
empleadoFROM pedidos
GROUPBY repPIVOTmonth(fecha
pedido)
El operador PIVOT sólo trabaja con base de datos cuyo nivel de compatibilidad mayor o igual a 90 (SQL Server 2005). Northwind es una base de datos creada con SQL Server 2000, y por lo tanto su nivel de compatibilidad es 80. Para solucionar este problema debemos obviamente cambiar dicho nivel de compatibilidad a 90.Msg 325, Level 15, State 1, Line 13 Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.
EJEMPLO EN LA BD NORTHWIND
EXEC dbo.sp_dbcmptlevel @dbname=N'Northwind', @new_cmptlevel=90
DECLARE @CatPVT AS NVARCHAR(MAX), @Categorias AS varchar(20)DECLARE @CatID INT --Creamos variables para almacenar la cadena,--las categorias,el id de las categotiasSET @CatID=(SELECT MIN(CategoryID) FROM Categories)SET @Categorias = ( SELECT CategoryName FROM Categories WHERE CategoryID = @CatID)--la categoria con el id mas bajo que seria idcategoria 1 BeveragesSET @CatPVT = N''--la cadena donde acumularemos las columnas que queremos tener al final del pivotWHILE @Categorias IS NOT NULLBEGIN SET @CatPVT = @CatPVT + N',['+ @Categorias +N']' --se añade el nombre almacenado SET @Categorias = (SELECT TOP(1) CategoryName FROM Categories WHERE CategoryID > @CatID ORDER BY CategoryID ASC)--se selecciona el siguiente nombre dentro de la tabla categorias SET @CatID=(SELECT MIN(CategoryID) FROM Categories Where Categoryname=@Categorias)--se reemplaza el ultimo id por el id que se acaba de aumentar--y se repite el Ciclo END
print @CatPVTSET @CatPVT = SUBSTRING(@CatPVT, 2, LEN(@CatPVT))print 'ok'print @CatPVT --se le quita la , al incicio de la cadenaDECLARE @sql AS nvarchar(MAX)--aqui tenemos la cadena que se ejecutara --y a donde se le añadira la subcadena que contiene las columnasSET @sql = N'SELECT * FROM (SELECT P.ProductID, C.CategoryName, (OD.UnitPrice * OD.Quantity) AS Monto FROM Products P INNER JOIN dbo.[Order Details] OD ON P.ProductID=OD.ProductID INNER JOIN Categories C ON C.CategoryID=P.CategoryID ) PIV PIVOT (SUM(Monto) FOR CategoryName IN ('+ @CatPVT + ')) AS Child order by 1' print @sqlEXEC sp_executesql @sql
Esto es especialmente útil cuando es imposible mediante una sentencia el rescatar toda la información que el usuario requiere, como por ejemplo en una factura del servicio telefónico que está sujeta a promociones, tipos de cliente, tipos de llamadas, localidades, horarios pico y no pico, etc.
CustomerID Year TotalDue ----------- ----------- --------------------- 1 2001 14603,7393 1 2001 26128,8674 1 2002 37643,1378 1 2002 34722,9906 2 2002 10184,0774 2 2002 5469,5941 2 2003 1739,4078 2 2003 1935,5166 2 2003 3905,2547 2 2003 4537,8484 2 2004 4053,9506 2 2004 908,3199 3 2004 17051,8292 3 2004 34873,5257
CustomerID 2001 2002 2003 2004 ----------- - -------------------- --------------------- - -------------------- -------------------- 1 40732,6067 72366,1284 NULL NULL 2 NULL 15653,6715 12118,0275 4962,2705 3 39752,8421 168393,7021 219434,4265 51925,3549 4 NULL 263025,3113 373484,299 143525,6018 5 NULL 33370,6901 60206,9999 20641,1106 6 NULL NULL 668,4861 2979,3473 7 NULL 6651,036 3718,7804 NULL 8 NULL NULL 19439,2466 10900,0347 9 NULL 320,6283 11401,5975 5282,8652 10 NULL 96701,7401 291472,2172 204525,9634 11 40350,4474 24300,4254 NULL NULL 12 NULL 117419,735 191505,7911 29091,7653 14 NULL NULL 7348,0162 1446,6848
VISITANOS ENhttp://jhacs.blogspot.com
http://grupotwo.wordpress.com
http://grupodos.net23.net
http://jhacs.wikispaces.com
GRACIAS