X,Y select query from one column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

X,Y select query from one column

Author

Topic

HECTOR CORELLA
Starting Member

2 Posts

Posted - 2008-02-21 : 23:20:16

Hey guys, here's what I have.
I have a table thats structured as.

date
hour
Value
tagName

The data has 2 diferent tag names, one of them is X the other a Y. I'm trying to build a query that would give a result as

date time valueoftagnameX valueoftagnameY

The data is collected every hour (60 values 1 per minute) and then the value is averaged for each of the tag.
The resulting table is something like this

example:
10/01/2007 10 45.4526 PR4567
10/01/2007 11 425.536 FR4267
10/01/2007 10 45.4526 PR4567
10/01/2007 11 421.36 FR4267
I've tried a UNION query with aliases but the result is not what I'm looking for. One row has the X value, the next the Y value and so on.

See what you think.
Appreciate the help
HC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-21 : 23:29:18

Hope this what you are looking for:-

SELECT date,hour,
       SUM(CASE WHEN tagName='PR4567' THEN value ELSE 0 END) AS PR4567value,
       SUM(CASE WHEN tagName='FR4267' THEN value ELSE 0 END) AS FR4267value
FROM YourTable
GROUP BY date,hour

HECTOR CORELLA
Starting Member

2 Posts

Posted - 2008-02-22 : 11:13:36

With a little tweaking got it to work.
Thank you
visakh16

quote:

Originally posted by visakh16

Hope this what you are looking for:-

SELECT date,hour,
       SUM(CASE WHEN tagName='PR4567' THEN value ELSE 0 END) AS PR4567value,
       SUM(CASE WHEN tagName='FR4267' THEN value ELSE 0 END) AS FR4267value
FROM YourTable
GROUP BY date,hour

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-22 : 11:22:19

quote:
Originally posted by HECTOR CORELLA

With a little tweaking got it to work.
Thank you
visakh16

quote:
Originally posted by visakh16

Hope this what you are looking for:-

SELECT date,hour,
SUM(CASE WHEN tagName='PR4567' THEN value ELSE 0 END) AS PR4567value,
SUM(CASE WHEN tagName='FR4267' THEN value ELSE 0 END) AS FR4267value
FROM YourTable
GROUP BY date,hour

You are welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources