My problem:
I have a table with a Channel <int> and a Value <float> column, along with a timestamp and a couple of other columns with additional data. Channel is either 1 or 2, and there is either 1 or 2 rows that have everything except channel and value the same.
What I'd like to do is select this data into a new form, where the two channels show up as columns. I tried to do something with GROUP BY, but I couldn't figure out how to get the values into the correct columns based on the channel on the same row.
Example:
For those of you that rather look at the data I have and the data I want and figure it out from there, here it is. What I have:
Channel Value Timestamp OtherStuff
1 0.2394 2010-07-09 13:00:00 'some other stuff'
2 1.2348 2010-07-09 13:00:00 'some other stuff'
1 24.2348 2010-07-09 12:58:00 'some other stuff'
2 16.3728 2010-07-09 12:58:00 'some other stuff'
1 12.284 2010-07-09 13:00:00 'unrelated things'
2 9.6147 2010-07-09 13:00:00 'unrelated things'
What I want:
Value1 Value2 Timestamp OtherStuff
0.2394 1.2348 2010-07-09 13:00:00 'some other stuff'
24.2348 16.3728 2010-07-09 12:58:00 'some other stuff'
12.284 9.6147 2010-07-09 13:00:00 'unrelated things'
Update in response to some questions that have arised in comments, and a few follow up questions/clarifications:
Yes, it is the combination of Timestamp and OtherStuff that links the two rows together. (OtherStuff is actually more than one column, but I simplified for brevity.) There are also a couple of other columns that are not necessarily equal, but should be kept just as they are.
The table in question is already joined from two tables, where Value, Channel and Timestamp comes from one of them, and the rest (a total of 7 more columns, out of which 4 are always equal for "linked" rows, and the other three are mostly not). There have been a couple of suggestions using INNER JOIN - will these still work if I'm already joining stuff together (even though I don't have a myTable to join to itself)?
There are a lot of rows with the same timestamp, so I need information from both the tables I'm joining to figure out which rows to link together.
I have a lot of data. The input comes from measurement devices stationed all over the country, and most of them (if not all) upload measurements (for up to 4 channels) every 2 minutes. Right now we have about 1000 devices online, so this means an addidtion of on average approximately 1000 rows every minute. I need to consider values that are up to at least 3, preferrably 6, hours old, which means 180 000 to 360 000 rows in the table with channel, value and timestamp.
As long as you have something that links the 2 rows, something like this
SELECT
c1.Value AS Value1, c2.Value AS Value2, c1.timestamp, c2.otherstuff
FROM
MyTable c1
JOIN
MyTable c2 ON c1.timestamp = c2.timestamp AND c1.otherstuff = c2.otherstuff
WHERE
c1.Channel = 1 AND c2.Channel = 2
If you don't have anything that links the 2 rows, then it probably can't be done because how do you know they are paired?
If you have 1 or 2 rows (edit: and don't know which channel value you have)
SELECT
c1.Value AS Value1, c2.Value AS Value2, c1.timestamp, c2.otherstuff
FROM
(
SELECT Value, timestamp, otherstuff
FROM MyTable
WHERE Channel = 1
) c1
FULL OUTER JOIN
(
SELECT Value, timestamp, otherstuff
FROM MyTable
WHERE Channel = 2
) c2 ON c1.timestamp = c2.timestamp AND c1.otherstuff = c2.otherstuff