Ver Mensaje Individual
  #17 (permalink)  
Antiguo 22/09/2011, 12:20
Avatar de Libras
Libras
Colaborador
 
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.404
Antigüedad: 13 años, 2 meses
Puntos: 774
Respuesta: Query cruzada

Código SQL:
Ver original
  1. CREATE TABLE #temp
  2. (
  3. pointid INT,
  4. lanid INT,
  5. thelabel VARCHAR(30),
  6. datatime datetime,
  7. datavalue VARCHAR(10),
  8. theunits VARCHAR(10),
  9. Expr1 VARCHAR(10)
  10. )
  11.  
  12.  
  13.  
  14. INSERT INTO #temp VALUES (580,8,'Hotel Columbus','10/07/2011 11:00','49,21','DegC','TEP01')
  15. INSERT INTO #temp VALUES (581,9,'Hotel Columbus2','10/08/2011 11:00','49,21','DegC2','TEP02')
  16. INSERT INTO #temp VALUES (581,9,'Hotel Columbus3','10/08/2011 11:00','49,21','DegC2','TEP03')
  17.  
  18.  
  19. SELECT pointid,lanid,thelabel,datatime,datavalue,theunits,[TEP01],[TEP02],[TEP03]
  20. FROM
  21. (
  22.    SELECT Expr1,pointid,lanid,thelabel,datatime,datavalue,theunits FROM #temp
  23. ) AS SOURCE
  24. pivot
  25. (
  26.   MAX(Expr1) FOR Expr1 IN ([TEP01],[TEP02],[TEP03])
  27. ) AS pvt

Resultado:


pointid lanid thelabel datatime datavalue theunits TEP01 TEP02 TEP03
580 8 Hotel Columbus 2011-10-07 11:00:00.000 49,21 DegC TEP01 NULL NULL
581 9 Hotel Columbus2 2011-10-08 11:00:00.000 49,21 DegC2 NULL TEP02 NULL
581 9 Hotel Columbus3 2011-10-08 11:00:00.000 49,21 DegC2 NULL NULL TEP03


Saludos!
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me