Query cruzada

[Libras]

Código SQL:

CREATE TABLE #temp
(
pointid INT,
lanid INT,
thelabel VARCHAR(30),
datatime datetime,
datavalue VARCHAR(10),
theunits VARCHAR(10),
Expr1 VARCHAR(10)
)
 
 
 
INSERT INTO #temp VALUES (580,8,'Hotel Columbus','10/07/2011 11:00','49,21','DegC','TEP01')
INSERT INTO #temp VALUES (581,9,'Hotel Columbus2','10/08/2011 11:00','49,21','DegC2','TEP02')
INSERT INTO #temp VALUES (581,9,'Hotel Columbus3','10/08/2011 11:00','49,21','DegC2','TEP03')
 
 
SELECT pointid,lanid,thelabel,datatime,datavalue,theunits,[TEP01],[TEP02],[TEP03]
FROM
(
   SELECT Expr1,pointid,lanid,thelabel,datatime,datavalue,theunits FROM #temp
) AS SOURCE
pivot
(
  MAX(Expr1) FOR Expr1 IN ([TEP01],[TEP02],[TEP03])
) 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!