Query cruzada

[iuliano]

Buenos días a todos.

Como puedo hacer una query para que en los campos de columna me aparezcan los valores de un campo.
Por ejemplo yo tengo un campo fecha, un campo nombrehotel y campo sensor y campo energía, yo quiero que en la query me aparezca:
fecha nombre hotel sensor1 sensor2 sensor3 sensor4
ens-------- ---------------- ---------- ----------- ----------- ----------
valor1 hotel columbus 10 50 10
valor2 hotel columbus 20 60
valor3 hotel x 30 70
''''''
'''''
valorn

Ahora mi query es la siguiente:
SELECT PointValue.PointID, Lans.LanId, Outstations.Thelabel, PointValue.DataTime, PointValue.DataValue, StrategyList.theLabel
FROM Lans INNER JOIN
Outstations ON Lans.lanID = Outstations.lanID INNER JOIN
StrategyList ON Outstations.outstationID = StrategyList.outstationID INNER JOIN
PointValue ON StrategyList.theIndex = PointValue.PointID INNER JOIN
SiteDetails ON Lans.siteID = SiteDetails.siteID
WHERE (Lans.LanNo = 20) AND (Outstations.NodeAddress= 20) AND (PointValue.DataTime >= '2011-06-17 04:00:00.000') AND (PointValue.DataTime <='2011-06-17 05:15:00.000')

[Libras]

Revisa la funcion Pivot

Saludos!

[iuliano]

ok, buscare información de pivot.

Muchas gracias.

Un saludo.

[iislas]

Claro esta, que si tu version es 2005 o superior

[iuliano]

No me reconoce la función PIVOT, haciendo la query desde sql server management studio express

[iuliano]

SELECT Outstations.Thelabel, PointValue.DataTime, PointValue.DataValue, StrategyList.theLabel
FROM Lans INNER JOIN
Outstations ON Lans.lanID = Outstations.lanID INNER JOIN
StrategyList ON Outstations.outstationID = StrategyList.outstationID INNER JOIN
PointValue ON StrategyList.theIndex = PointValue.PointID INNER JOIN
SiteDetails ON Lans.siteID = SiteDetails.siteID
WHERE (Lans.LanNo = 20) AND (Outstations.NodeAddress= 20) AND (Outstations.Thelabel= 'Hotel Columbus') AND (PointValue.DataTime >= '2011-08-01 00:00:00.000') AND (PointValue.DataTime <='2011-08-31 23:45:00.000')

Quiero que los valores de strategyList.theLabel me aparezcan como columnas y la función pivot no me la coge.

[iislas]

¿Version 2005 o superior?

[iuliano]

Versión 2005

[Libras]

como estas usando la funcion pivot??

[iuliano]

La estoy usando al final de la query pongo pivot y los campos que quiero me aparezcan sus valores como columnas.

[Libras]

por eso pon el ejemplo de como lo estas usando(query)

[iuliano]

la estoy usando así:
TRANSFORM Sum(Query.[DataValue]) AS SumaDeDataValue
SELECT Query.[dbo_Outstations].[theLabel], Query.[DataTime]
FROM Query
GROUP BY Query.[dbo_Outstations].[theLabel], Query.[DataTime]
PIVOT Query.[dbo_StrategyList].[theLabel]

En Access si funciona y ahora he creado un proyecto de access y ahí no me funciona??

Es sql express edition 2005.

[Libras]

ya revisaste esta liga?

http://msdn.microsoft.com/en-us/library/ms177410.aspx

Saludos!

[iuliano]

Si la he revisado pero no me funciona.

La función transform en sql no funciona, pero como pone en esa liga, tampoco me funciona.
Que puede ocurrir??

[Libras]

Probe esta funcion en sql express 2005:

Código SQL:

--Create the table and insert values as portrayed in the previous example.
DROP TABLE #pvt
CREATE TABLE #pvt (VendorID INT, Emp1 INT, Emp2 INT,
    Emp3 INT, Emp4 INT, Emp5 INT);
GO
INSERT INTO #pvt VALUES (1,4,3,5,4,4);
INSERT INTO #pvt VALUES (2,4,1,5,5,5);
INSERT INTO #pvt VALUES (3,4,3,5,4,4);
INSERT INTO #pvt VALUES (4,4,2,5,5,4);
INSERT INTO #pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM #pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
 

Y si funciona, a ver un ejemplo de tus datos para ayudarte

[iuliano]

Yo tengo la query creada ya con los datos que quiero sacar, lo que quiero es que me los muestre cruzados.
No me hace falta crear la tabla, ni meterle valores quiero hacerlo a partir de la query.
Esta es la query inicial:
SELECT dbo.PointValue.PointID, dbo.Lans.lanID, dbo.Outstations.theLabel, dbo.PointValue.DataTime, dbo.PointValue.DataValue, dbo.StrategyList.theUnits,
dbo.StrategyList.theLabel AS Expr1
FROM dbo.Lans INNER JOIN
dbo.Outstations ON dbo.Lans.lanID = dbo.Outstations.lanID INNER JOIN
dbo.StrategyList ON dbo.Outstations.outstationID = dbo.StrategyList.outstationID INNER JOIN
dbo.PointValue ON dbo.StrategyList.theIndex = dbo.PointValue.PointID INNER JOIN
dbo.SiteDetails ON dbo.Lans.siteID = dbo.SiteDetails.siteID
WHERE (dbo.Lans.LanNo = 20) AND (dbo.Outstations.NodeAddress = 20)

PointID lanID theLabel DataTime DataValue theUnits Expr1
580 8 Hotel Columbus 10/07/2011 11:00 49,21 DegC TEP01

Y lo que quiero es que me ponga como columnas los campos de Expr1 (TEPO1 TEP02 TEP03)

[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!

[iislas]

Libras, mis respetos por su cordura, temple, paciencia y ganas de ayudar.

[Libras]

Iniciado por iislas

Libras, mis respetos por su cordura, temple, paciencia y ganas de ayudar.

Muchas Gracias iislas, mas que ganas de ayudar son ganas de aprender cosas nuevas(como esto del pivot que solo habia visto teoricamente) y porque no compartir lo poco o mucho que tengo de conocimientos :)

[iuliano]

Muchas gracias Libras por toda tu ayuda.

Yo los valores que tu pones en el primer select de tep01, tep02, esos valores los tengo en el campo Expr1, y quiero que me los muestre en columnas. No creo que tenga que escribir en el select todos los valores a mano no??

Un saludo y de nuevo gracias.

[Libras]

Yo los valores que tu pones en el primer select de tep01, tep02, esos valores los tengo en el campo Expr1

Pues esos valores los saque del campo expr1

pivot
(
max(Expr1) FOR Expr1 IN ([TEP01],[TEP02],[TEP03])
) AS pvt


nada mas que tienes que especificar que columnas y que valores son los que necesitas que te ponga en una tabla pivote, probaste el query???

Saludos!