String aggregation in SQL2k5

Question

Post reply

String aggregation in SQL2k5

K-SQL-SSC

Ten Centuries

Points: 1066
More actions
March 11, 2008 at 1:57 pm

#184942

Hi All,
I have to working with the String Aggregation for my result set from a query. So, in SQL Server 2005 is there any String Aggregation functions available that I can use it. OR I have to handle String Aggregation in my VB.Net Code?
I have to aggregate the some of the fileds from my quiery's resultset. Data type of thoese fileld are varchar.
So, If any one have ideo or having some techniques then please help me... I will really appreciate all of you.
Regards,
KP

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 1

You can use CAST or CONVERT - i.e.

create table #Blah

( SomeValue varchar(5))

insert into #Blah (SomeValue)

values(4)

go

insert into #Blah (SomeValue)

values(5)

go

select Sum(CAST(SomeValue as int)) as Total

from #blah

select AVG(CAST(SomeValue as int)) as Average

from #blah

go

Tommy

Follow @sqlscribe

K-SQL-SSC Ten Centuries Points: 1066 More actions · Answer 2

Thanks for your reply. I already tried Cast and Convert function but it is not working. I have alphanumeric data in that field. The datatype is varchar(60). So, it is unabale to conver the datatype.

Is there any other or alrenate solution?

Please help me....

XPSCodes-667952 SSChasing Mays Points: 603 More actions · Answer 3

Aggregate in the sense concatenate rows? Or :unsure:

--------------------------------------------------------------------------------------
Save our world, its all we have! A must watch video Pale Blue Dot[/url]

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Ketulp... there's a couple of ways to do this... here's one full example that demo's two of the fastest ways...

--===== Create and populate test table. THIS IS NOT PART OF EITHER SOLUTION!

-- Column "RowNum" contains unique row numbers

-- Column "SomeID" contains non-unique numbers

-- (should be 1/400th of the row count to allow about 400 concatenated items per SomeInt).

-- Column "SomeCode" has a range of "AA" to "ZZ" non-unique 2 character strings

SELECT TOP 1000000 --<<<LOOK! CHANGE THIS NUMBER TO CHANGE THE NUMBER OF ROWS!

RowNum = IDENTITY(INT,1,1),

SomeID = ABS(CHECKSUM(NEWID()))%2500+1, --<<<LOOK! CHANGE THIS NUMBER TO 1/400th THE ROW COUNT

SomeCode = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)

INTO dbo.TestData

FROM Master.dbo.SysColumns t1,

Master.dbo.SysColumns t2

--===== A table is not properly formed unless a Primary Key has been assigned

-- THIS IS NOT PART OF EITHER SOLUTION!

ALTER TABLE dbo.TestData

ADD PRIMARY KEY CLUSTERED (RowNum)

--===== Add the index to support both types of queries

-- THIS IS PART OF ONE SOLUTION!

CREATE INDEX IXC_TestData_Cover1 ON dbo.TestData(SomeID,SomeCode)

GO

-- THIS IS PART OF ONE SOLUTION!

CREATE FUNCTION dbo.fnConcatTest (@SomeID INT)

RETURNS VARCHAR(8000) AS

BEGIN

DECLARE @Return VARCHAR(8000)

SELECT @Return = ISNULL(@Return+',','')+SomeCode

FROM dbo.TestData

WHERE SomeID = @SomeID

RETURN @Return

END

GO

--===== Supress the auto-display of rowcounts for appearance

-- THESE ARE THE SOLUTIONS!

SET NOCOUNT ON

--===== Test and time the "Ugly stick" method

PRINT REPLICATE('=',80)

PRINT '"Ugly trick" code...'

SET STATISTICS IO ON

SELECT SomeID, dbo.fnConcatTest(SomeID) AS CSVString

FROM dbo.TestData

GROUP BY SomeID

SET STATISTICS IO OFF

--===== Test and time the XML method

PRINT REPLICATE('=',80)

PRINT 'XML code...'

SET STATISTICS IO ON

SELECT t1.SomeID,

STUFF(

(SELECT ',' + t2.SomeCode

FROM TestData t2

WHERE t2.SomeID = t1.SomeID

FOR XML PATH('')),

1, 1, '') AS CsvString

FROM TestData t1

GROUP BY t1.SomeID

SET STATISTICS IO OFF

PRINT REPLICATE('=',80)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

K-SQL-SSC Ten Centuries Points: 1066 More actions · Answer 5

Yes, I have to do the Sting concating, I have to conver sigle raw from the multiple raws.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

Then, pick one of the two methods in the code I demo'd above...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)