SQL Help

[kavita11]

Hi,

I have this SQL query to write..and I have no Idea how to do this..any help would be great..

I have a table called

logtable

the following are the fields:

data_name,
timestamp

data_i_value
data_f_Value
data_s_value


------------

fields data_name and time_stamp together forms the PK.


so time_stamp can be same for different data_names.
data_names can occure multiple times for different time_stamp.


I want to write a SQL statement (query, view, stored proc, or a cursor..whichever is suitable)
which can get me the data in the form of a view..

eg.

at present this is how my data looks like

Data_NAME TIMESTAMP DATA_F_VALUE DATA_I_VALUE DATA_S_VALUE

alpha 4/1/2005 15:28 20.1 0
beta 4/1/2005 15:29 25.1 0
gamma 4/1/2005 15:29 27.1 0
alpha 4/1/2005 15:30 20.1 0
beta 4/1/2005 15:30 27.7 0
gamma 4/1/2005 15:30 20.1 0
alpha 4/1/2005 15:31 27.5 0
beta 4/1/2005 15:31 25.4 0
gamma 4/1/2005 15:31 23.2 0




and This is how i want it too look, after running my query...

Time_STamp alpha beta gamma
4/1/2005 15:28 20.1
4/1/2005 15:29 25.1 27.1
4/1/2005 15:30 20.1 27.7 20.1
4/1/2005 15:31 27.5 25.4 23.2

Please Help.
I have no idea how to start.

Thanks.

 

[Catadmin]

Look up Create View and Select in Books Online. Your columns can be aliased within the Select part of the statement as "data_f_value as Alpha".



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[corningIT]

Try this out (uses subqueries)


SELECT

Main.TimeStamp 'TimeStamp',
ISNULL(A.Alpha,0) 'Alpha',
ISNULL(B.Beta,0) 'Beta',
ISNULL(G.Gamma,0) 'Gamma'

FROM

logtable Main

LEFT JOIN

(select TimeStamp,DATA_F_VALUE AS [Alpha] from logtable where Data_Name='alpha') A
on Main.TimeStamp=A.TimeStamp

LEFT JOIN

(select TimeStamp,DATA_F_VALUE AS [Beta] from logtable
where Data_Name='beta') B
on Main.TimeStamp=B.TimeStamp

LEFT JOIN

(select TimeStamp,DATA_F_VALUE AS [Gamma] from logtable
where Data_Name='gamma') G
on Main.TimeStamp=G.TimeStamp

ORDER BY Main.TimeStamp