quote:

---

Originally posted by sqlbug

This is in reference to my earlier posting "Modifying the query". Here's what I wrote:
We have two tables – lets call them SAMPLE_DATA and REAL_DATA tables containing basic information about the data and details data, respectively. Sample_num from the sample_data is the foreign key in real_data table.
In the real_data table, data for each hour of the day are stored in separate rows. Before, we had one sample number for the whole day, for a plot. Now, we have 24 sample numbers for the 24 hours of the day for a plot. As a result – we need to change the query that shows output on a webpage as a table of all 24 values for a day in each row. Because (reminder) – before, we had one sample number for a day for a plot, now we have 24 sample number for 24 hourly values.
Here’s what we have now in short:

SELECT RDATA_SAMPLE_NUM, METHOD_NAME Method, UNIT_ABBREV Unit, SUM(Hour1) AS Hour1, SUM(Hour2) AS Hour2,......SUM(Hour24) AS Hour24 FROM
(SELECT DISTINCT RDATA_SAMPLE_NUM, METHOD_NAME, UNIT_ABBREV,
(CASE RDATA_HOUR WHEN 1 THEN ISNULL(CONVERT(decimal(4,4),RDATA_VALUE/10000.0), 0) END) AS Hour1,
(CASE RDATA_HOUR WHEN 2 THEN ISNULL(CONVERT(decimal(4,4),RDATA_VALUE/10000.0), 0) END) AS Hour2,......
(CASE RDATA_HOUR WHEN 24 THEN ISNULL(CONVERT(decimal(4,4),RDATA_VALUE/10000.0), 0) END) AS Hour24,
FROM REAL_DATA,SAMPLE_DATA,......
WHERE RDATA_SAMPLE_NUM = SAMPLE_NUM
AND DATEPART("MM", SAMPLE_START_DATE) = ...
AND DATEPART("YYYY", SAMPLE_START_DATE) = ...
GROUP BY ...) S
GROUP BY ...

Now, if I use DAY_NUM in the query instead of RDATA_SAMPLE_NUM (sample number) thats causing the problem now, I end up with multiple rows (like 24 rows for 24 hours of the day). If I could combine them, I would get what I wnated.
Any ideas? Thanks.

---