asked on

SQL for getting two values per result from one table

Hi all ... scratching my head over this, but I suspect experts here may know it ...

If a table has fields:

PersonID (integer)
GMTDate (date)
TimePeriod (integer)
Channel (integer)
ReadIngValue (real)

Lets say the data looks like this:

PersonID GMTDate TimePeriod Channel ReadingValue
1 15-05-2008 1 7 4.6
1 15-05-2008 1 8 2.6
1 15-05-2008 1 9 3.1
1 15-05-2008 2 7 2.9
1 15-05-2008 2 8 4.0
1 15-05-2008 2 9 4.1
1 16-05-2008 1 7 3.3
1 16-05-2008 1 8 3.7
1 16-05-2008 1 9 0.4
2 15-05-2008 1 7 2.2

... and I need to get results back into one line of an SQL query from where I identify a date range and PersonID, and want TWO ReadingValue results for twoChannel number = 7 and Channel Number = 9 ... so that the result looks like this:

PersonID GMTDate TimePeriod Channel7ReadingValue Channel9ReadingValue
1 15-05-2008 1 4.6 3.1
1 15-05-2008 2 2.9 4.1
1 16-05-2008 1 3.3 0.4

How should my SQL look?

Nerdwood

Hi MartinC

Try something like this:

SELECT DISTINCT     D.PersonID, D.GMTDate, D.TimePeriod, (
  SELECT ReadingValue
  FROM tblData 7
  WHERE 7.PersonID = D.PersonID
    AND 7.GMTDate = D.GMTDate
    AND 7.TimePeriod = D.TimePeriod
    AND 7.Channel = 7
) AS Channel7ReadingValue, (
  SELECT ReadingValue
  FROM tblData 9
  WHERE 9.PersonID = D.PersonID
    AND 9.GMTDate = D.GMTDate
    AND 9.TimePeriod = D.TimePeriod
    AND 9.Channel = 9
) AS Channel9ReadingValue
FROM         tblData D

Open in new window

Nerdwood

Sorry, here's the version that works! Just had to re-create your table...

SELECT DISTINCT D.PersonID, D.GMTDate, D.TimePeriod, (
  SELECT ReadingValue
  FROM tblData tblData7
  WHERE tblData7.PersonID = D.PersonID
    AND tblData7.GMTDate = D.GMTDate
    AND tblData7.TimePeriod = D.TimePeriod
    AND tblData7.Channel = 7
) AS Channel7ReadingValue, (
  SELECT ReadingValue
  FROM tblData tblData9
  WHERE tblData9.PersonID = D.PersonID
    AND tblData9.GMTDate = D.GMTDate
    AND tblData9.TimePeriod = D.TimePeriod
    AND tblData9.Channel = 9
) AS Channel9ReadingValue
FROM         tblData D

Open in new window

Thomasian

hi MartinC,

Try

SELECT PersonID, GMTDate, TimePeriod
      ,SUM(CASE WHEN Channel=7 THEN ReadingValue END) Channel7ReadingValue
      ,SUM(CASE WHEN Channel=9 THEN ReadingValue END) Channel9ReadingValue
FROM tablename
WHERE Channel IN (7,9)
GROUP BY PersonID, GMTDate, TimePeriod

Open in new window

rstomar

use PIVOT

rstomar

with PIVOT column values can be shown as columns

ASKER CERTIFIED SOLUTION

koutny

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MartinC

ASKER

Rats, my initial question left out a crucial grouping function ... so I have to confess I couldn't actually run these exact approaches. However koutny's response formed the basis for what I eventually used so I'll give the points there. Thanks everybody for your suggestions.