Complex SQL Query Question 1

[Guest_imported]

Hi

I have data in a table in the following format :

Time | ID | Value
10:51 1 1
10:51 2 5
10:51 3 2
10:52 1 7
10:52 2 1
10:52 3 3
10:53 1 2
10:53 2 7
10:53 3 3

What I would like to achieve is to apply a query to the above table that would produce a result dataset that puts each unique ID into it's own field with the corresponding Value in the rows below, based on time, eg :

Time | 1 | 2 | 3
10:51 1 5 7
10:52 7 1 3
10:53 2 7 3

Thank You

 

[tlbroadbent]

If you always have 3 values per time period this solution works.

Select
a.[Time],
a.[Value] As V1,
b.[Value] As V2,
c.[Value] As V3
From
(Select [Time], [Value]
From TableName
Where ID=1) As a
Inner Join
(Select [Time], [Value]
From TableName
Where ID=2) As b
Inner Join
(Select [Time], [Value]
From TableName
Where ID=3) As c

If the data structure isn't as described you can use a similar technique but would need to use Left Joins. Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[PruSQLer]

Hey Broadbent,

Inspired as usual but don't you need to do the Inner Join on Time so as to link up properly?

 

[crufty]

I agree with PruSQLer, you have to create the complete set of distinct time units and use this as the base join table in order to get a complete set of data.

In pseudo code (excuse the oracle flavor):

select a.time, b.item1, c.item2, ...
from
(select distinct time
from tablename) a,
(select time, item1
from tablename
where id = 1) b,
(select time, item2
from tablename
where id = 2) c,
:::::
where a.time = b.time(+) and a.time = c.time(+) and ...

(+) is Oracle for innier join.


This query structure will show all values (or lack of values) for all distinct time units.

 

[tlbroadbent]

Yes. I left off the ON clause. Sorry about that.

Select
a.[Time],
a.[Value] As V1,
b.[Value] As V2,
c.[Value] As V3
From
(Select [Time], [Value]
From TableName
Where ID=1) As a
Inner Join
(Select [Time], [Value]
From TableName
Where ID=2) As b
On a.[Time]=b.[Time]
Inner Join
(Select [Time], [Value]
From TableName
Where ID=3) As c
On a.[Time]=c.[Time] Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[bperry]

This will work in MS SQL Server, and will be quite a bit more efficient. (ie fewer table scans) Not sure if this syntax will work in Oracle, tho.

Select
Time,
SUM(CASE When ID=1 Then Value Else 0 END) as Val1,
SUM(CASE When ID=2 Then Value Else 0 END) as Val2,
SUM(CASE When ID=3 Then Value Else 0 END) as Val3
From TableName
Group By Time
Order By Time