CONSULTAS DE REFERENCIAS CRUZADAS

1. CONSULTAS DE REFERENCIAS CRUZADAS

2. ANGULO MENDEZ, Angelo LEVANO CASTILLA, Carlos PARDO FIGUEROA HERENCIA, Jhonatan QUISPE ARCOS, Hans RAMIREZ GAMBOA, Marlon INTEGRANTES

3. Consultas de Referencia Cruzada

4. La sentencia TRANSFORM es la que se utiliza para definir una consulta de referencias cruzadas. La sintaxis es la siguiente: La sentencia SELECT En 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 PIVOT En 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.

6. EJEMPLO EN LA BD NORTHWIND 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. EXECdbo.sp_dbcmptlevel @dbname=N'Northwind', @new_cmptlevel=90

8. DECLARE @CatPVTASNVARCHAR(MAX), @CategoriasASvarchar(20) • DECLARE @CatIDINT • --Creamos variables para almacenar la cadena, • --las categorias,el id de las categotias • SET @CatID=(SELECTMIN(CategoryID)FROM Categories) • SET @Categorias=(SELECTCategoryNameFROM Categories WHERECategoryID= @CatID) • --la categoria con el id mas bajo que seria idcategoria 1 Beverages • SET @CatPVT=N'' • --la cadena donde acumularemos las columnas que queremos tener al final del pivot • WHILE @CategoriasISNOTNULL • BEGIN • SET @CatPVT= @CatPVT+N',['+ @Categorias+N']' • --se añade el nombre almacenado • SET @Categorias=(SELECTTOP(1)CategoryName • FROM Categories WHERECategoryID> @CatID • ORDERBYCategoryIDASC) • --se selecciona el siguiente nombre dentro de la tabla categorias • SET @CatID=(SELECTMIN(CategoryID)FROM Categories WhereCategoryname=@Categorias) • --se reemplaza el ultimo id por el id que se acaba de aumentar • --y se repite el Ciclo • END

9. print @CatPVT • SET @CatPVT=SUBSTRING(@CatPVT, 2,LEN(@CatPVT)) • print'ok' • print @CatPVT • --se le quita la , al incicio de la cadena • DECLARE @sqlASnvarchar(MAX) • --aqui tenemos la cadena que se ejecutara • --y a donde se le añadira la subcadena que contiene las columnas • SET @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 @sql • EXECsp_executesql@sql

14. 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 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.

15. VISITANOS EN http://jhacs.blogspot.com http://grupotwo.wordpress.com http://grupodos.net23.net http://jhacs.wikispaces.com

16. GRACIAS