Tricky SQL SELECT statement - combine two rows into two columns

Question

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.

Answer 1

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                  

Answer 2

SELECT a.Value as Value1, b.Value as Value2,
a.TimeStamp, a.OtherStuff
FROM myTable a INNER JOIN myTable b
ON a.OtherStuff = b.OtherStuff and a.TimeStamp = b.TimeStamp
WHERE a.Channel = 1 AND b.Channel = 2

Written without a query editor. Please be kind :)

EDIT: INNER JOIN could also be used here. EDIT2: Corrected with INNER JOIN . Don't rush to downvote :)

Answer 3

Something like...

SELECT   MAX(CASE Channel WHEN 1 THEN Value ELSE 0 END) AS Value1,
         MAX(CASE Channel WHEN 2 THEN Value ELSE 0 END) AS Value2,
         Timestamp, 
         OtherStuff
FROM     {tablename}
GROUP BY Timestamp, OtherStuff

(I havent tested this!) (and this assumes your Value is always positive!)

Alternatively (see comments below)...

SELECT   SUM(CASE Channel WHEN 1 THEN Value ELSE 0 END) AS Value1, 
         SUM(CASE Channel WHEN 2 THEN Value ELSE 0 END) AS Value2, 
         Timestamp, 
         OtherStuff 
FROM     {tablename}
GROUP BY Timestamp, OtherStuff