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