Help to create a view - SQL Server Forums

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

Transact-SQL (2000)

Help to create a view

Author

Topic

Erakis
Starting Member

1 Post

Posted - 2006-01-16 : 16:28:41

Hello,

I have a table that contains many value for many sensors. Here is an short exemple :


[DATE, SENSOR_ID, VALUE] 
... 
"2006-01-01 00:00:00", 8, 8.76 
"2006-01-01 00:00:00", 10, 8.41 
"2006-01-01 00:00:00", 11, 0.45 
"2006-01-01 00:00:00", 12, 66.63 
"2006-01-01 00:00:00", 13, 5.04 
"2006-01-01 00:00:00", 14, 149.72 
"2006-01-01 00:05:00", 8, 8.76 
"2006-01-01 00:05:00", 9, 0.41 
"2006-01-01 00:05:00", 10, 8.41 
"2006-01-01 00:05:00", 11, 0.47 
"2006-01-01 00:05:00", 13, 5.02 
...

Also I have a table containing all sensors (ID, Name, Description, ...)
From the first table I want to sort data as :


[DATE, SENSOR 1, SENSOR 2, SENSOR 3, SENSOR 4, ... ] 
2006-01-01 00:00:00, 8.76, 8.41, 0.45, 66.63, 5.04, 149.72 
2006-01-01 00:05:00, 8.76, 8.41, 8.47, NULL, 5.02, ...

Is it possible and how ?
Many thanks.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-16 : 19:42:12

Little bit confused with the data
1. What is in 2nd table ? and what is the relevance of that to the Question?
2. What is the basis of getting the results (for each distinct date/time value, change col data to rows ???)
3. If 2 is correct what is the significance of Sensor_ID

I think the following link will help u (if ur need is above 2)
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-16 : 20:35:22

try this..

select 	[date],
	max(case when sensor_id = 8 then value else null end) as sensor_1,
	max(case when sensor_id = 9 then value else null end) as sensor_2,
	max(case when sensor_id = 10 then value else null end) as sensor_3,
	max(case when sensor_id = 11 then value else null end) as sensor_4,
	max(case when sensor_id = 12 then value else null end) as sensor_5,
	max(case when sensor_id = 13 then value else null end) as sensor_6,
	max(case when sensor_id = 14 then value else null end) as sensor_7
from 	#sensor
group by [date]

Also refere to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210 on cross-tab, pivot table

-----------------
'KH'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-17 : 01:36:58

Also refer this for more Cross tabs
http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources