String Aggregation in SQL Server

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

String Aggregation in SQL Server

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2008-08-19 : 13:25:23

I know string aggregation is not supported in SQL Server. But I have this situation: I'm creating(retrieving) 24 columns from one column in a table based on the value. That column has numeric values. So, I could use aggregation. But I need to do the same for another column which is a char(1). I am trying a query like:
---------------------------
SELECT RDATA_SAMPLE_NUM, METHOD_NAME Method, UNIT_ABBREV Unit,
SUM(Hour1) AS Hour1,Flag1 AS Flag1,
....................
SUM(Hour24) AS Hour24,Flag24 AS Flag24,
AVG(Daily_AVG) AS Daily_AVG
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 1 THEN RDATA_FLAG END) AS Flag1,
..........
(CASE RDATA_HOUR WHEN 24 THEN ISNULL(CONVERT(decimal(4,4),RDATA_VALUE/10000.0), 0) END) AS Hour24,
(CASE RDATA_HOUR WHEN 24 THEN RDATA_FLAG END) AS Flag24,
CONVERT(decimal(4,4),AVG(CAST(RDATA_VALUE AS int)/10000.0)) AS 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 = 'Fort Saskatchewan-92 St and 96 Ave' AND PARAM_NAME = 'PM2.5 Mass'
AND DATEPART("MM", RSAMPLE_START_DATE) = 5
AND DATEPART("YYYY", RSAMPLE_START_DATE) = 2008
GROUP BY RDATA_SAMPLE_NUM,METHOD_NAME,UNIT_ABBREV,RDATA_HOUR,RDATA_VALUE,RDATA_FLAG) S
GROUP BY RDATA_SAMPLE_NUM,METHOD_NAME,UNIT_ABBREV,Flag1,Flag2,Flag3,Flag4,Flag5,Flag6,Flag7,Flag8,Flag9,
Flag10,Flag11,Flag12,Flag13,Flag14,Flag15,Flag16,Flag17,Flag18,Flag19,Flag20,Flag21,Flag22,Flag23,Flag24
-------------------------------------------------------------
It gives me lot of extra rows, while I need the rows aggregated (one row for one sample_num), because the Flag columns are not being aggregated.
Any way around this? Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 13:27:27

show some sample data and explain what you want.Its quite difficult to make out from query posted.

sqlbug
Posting Yak Master

201 Posts

Posted - 2008-08-19 : 14:00:56

In this sample output, you can see 2 rows with the same sample number (that should be merged in one), and they were merged before I added the flag columns - because I am using aggregate on all the rdata_value columns but I can't use any aggregate function on rdata_flag, as its a char(1) type.
----------------------------------------------
10338755,geom @ 40C,uv/m3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0135, ,NULL,NULL,0.013500
----------------------------------------------
10338755,geom @ 40C,uv/m3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0.0125, ,NULL,NULL,NULL,NULL,0.012500
----------------------------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 23:59:25

Why? Cant you apply MAX() or MIN()? what are possible flag values?

sqlbug
Posting Yak Master

201 Posts

Posted - 2008-08-20 : 11:27:50

Possible flag values are some letters like - M, D etc....therefore, MIN, MAX will not produce desired result.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 11:47:47

quote:
Originally posted by sqlbug

Possible flag values are some letters like - M, D etc....therefore, MIN, MAX will not produce desired result.

ok. whats the desired flag value result for you?

sqlbug
Posting Yak Master

201 Posts

Posted - 2008-08-20 : 13:09:14

The desired flag value is the exact value in the table column. Let me explain.
As you can see from the query (my first post), in the table - we do not have 24 columns for 24 hours in the day. What we have is:
Col1,Col2......Hour Value Flag
---- ----......---- ----- ----
So, for each hour and value, there is a corresponding flag value. In my query, I am getting the VALUE depending on the hour(1,2 etc) to display them under Hour1, Hour2..etc. columns. I want to do exactly the same for the FLAGs. The problem is - VALUE was numeric so, we could use SUM(..) on that. But we cannot do that on FLAG (it's a char type) - hence we are getting more rows with lot of null values because we are not able to use SUM(..) on the FLAG field to merge the rows.

Hope it helps.
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 13:17:19

but wont u be having a single flag value for a particular hour?

sqlbug
Posting Yak Master

201 Posts

Posted - 2008-08-20 : 14:11:06

That's right.
That's what I am trying to do. For Hour1, one value and a corresponding flag value (Flag1).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources