Merging Multiple Rows into one

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Merging Multiple Rows into one

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2008-08-01 : 15:36:11

Hi, I have this query and I want one row for one RDATA_SAMPLE_NUM.
SELECT distinct RDATA_SAMPLE_NUM,METHOD_NAME Method, UNIT_ABBREV Unit,
(CASE RDATA_HOUR WHEN 1 THEN CONVERT(NUMERIC(10,2),ISNULL(RDATA_VALUE, 0)) END) AS Hour1,
(CASE RDATA_HOUR WHEN 2 THEN ISNULL(RDATA_VALUE, 0) END) AS Hour2,
(CASE RDATA_HOUR WHEN 3 THEN ISNULL(RDATA_VALUE, 0) END) As Hour3,
(CASE RDATA_HOUR WHEN 4 THEN ISNULL(RDATA_VALUE, 0) END) AS Hour4,
(CASE RDATA_HOUR WHEN 5 THEN ISNULL(RDATA_VALUE, 0) END) AS Hour5,
(CASE RDATA_HOUR WHEN 6 THEN ISNULL(RDATA_VALUE, 0) END) AS Hour6,
(CASE RDATA_HOUR WHEN 7 THEN ISNULL(RDATA_VALUE, 0) END) As Hour7,
(CASE RDATA_HOUR WHEN 8 THEN ISNULL(RDATA_VALUE, 0) END) AS Hour8,
(CASE RDATA_HOUR WHEN 9 THEN ISNULL(RDATA_VALUE, 0) END) AS Hour9,
(CASE RDATA_HOUR WHEN 10 THEN ISNULL(RDATA_VALUE, 0) END) AS Hour10,
(CASE RDATA_HOUR WHEN 11 THEN ISNULL(RDATA_VALUE, 0) END) As Hour11,
(CASE RDATA_HOUR WHEN 12 THEN ISNULL(RDATA_VALUE, 0) END) AS Hour12,
(CASE RDATA_HOUR WHEN 13 THEN ISNULL(RDATA_VALUE, 0) END) AS Hour13,
(CASE RDATA_HOUR WHEN 14 THEN ISNULL(RDATA_VALUE, 0) END) AS Hour14,
(CASE RDATA_HOUR WHEN 15 THEN ISNULL(RDATA_VALUE, 0) END) As Hour15,
(CASE RDATA_HOUR WHEN 16 THEN ISNULL(RDATA_VALUE, 0) END) AS Hour16,
(CASE RDATA_HOUR WHEN 17 THEN ISNULL(RDATA_VALUE, 0) END) AS Hour17,
(CASE RDATA_HOUR WHEN 18 THEN ISNULL(RDATA_VALUE, 0) END) AS Hour18,
(CASE RDATA_HOUR WHEN 19 THEN ISNULL(RDATA_VALUE, 0) END) As Hour19,
(CASE RDATA_HOUR WHEN 20 THEN ISNULL(RDATA_VALUE, 0) END) AS Hour20,
(CASE RDATA_HOUR WHEN 21 THEN ISNULL(RDATA_VALUE, 0) END) AS Hour21,
(CASE RDATA_HOUR WHEN 22 THEN ISNULL(RDATA_VALUE, 0) END) AS Hour22,
(CASE RDATA_HOUR WHEN 23 THEN ISNULL(RDATA_VALUE, 0) END) As Hour23,
(CASE RDATA_HOUR WHEN 24 THEN ISNULL(RDATA_VALUE, 0) END) AS Hour24,
AVG(RDATA_VALUE) Daily_Avg
FROM STATION_REALTIME_DATA,SAMPLE_REALTIME_DATA,METHOD_CODES,UNIT_CODES,
PARAMETER_INFO,STATION_INFO WHERE RDATA_SAMPLE_NUM = RSAMPLE_NUM
AND RDATA_METHOD = METHOD_CODE AND RDATA_UNIT = UNIT_CODE
AND STN_ID = RSAMPLE_STATION AND PARAM_ID = RDATA_PARAMETER
AND STN_NAME = 'Central' AND PARAM_NAME = 'Mass'
AND DATEPART("MM", RSAMPLE_START_DATE) = 12 (or any other value)
GROUP BY RDATA_SAMPLE_NUM,METHOD_NAME,UNIT_ABBREV,RDATA_HOUR,RDATA_VALUE

Any idea? I checked lot of materials online but the examples use only two tables for the join..etc that didn't look right for this query. I'll appreciate any help.
Thanks.

sqlbug
Posting Yak Master

201 Posts

Posted - 2008-08-01 : 16:12:11

Never mind, I got it.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-04 : 04:10:52

quote:
Originally posted by sqlbug

Never mind, I got it.

What is the solution?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources