Concatenate results into a string

[jkirkland]

I am new to SQL Server (from MS Access)

I would like to concatenate a recordset into a single string such as:

FieldA
-------
101
210
206

Into 101,210,206

I have stored procedure started but it returns a null value:
------------------------------------
DECLARE @CurrentRow int
DECLARE @RowCount int,
@strResult varchar(8000)

-- Insert into temp table
DECLARE @temptable table(Id int primary key identity(1,1), HTEFund int)

INSERT INTO @temptable (HTEFund)
SELECT tblAccountMaster.GMFUND
FROM tblFundSummarySettingsA INNER JOIN tblAccountMaster ON
tblFundSummarySettingsA.GMFUND = tblAccountMaster.FSFund
GROUP BY tblFundSummarySettingsA.GMFUND, tblAccountMaster.GMFUND

SELECT @CurrentRow = 1
SELECT @RowCount=COUNT(Id) FROM @temptable

--For each record in @temptable do...
WHILE @CurrentRow <= @RowCount
BEGIN

SET @strResult = @strResult + (
SELECT HTEFund
FROM @temptable
WHERE id = @CurrentRow

SELECT @CurrentRow = @CurrentRow + 1
END

RETURN @strResult

Thanks for the help!

 

[gmmastros]

Read this: thread183-1159740



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

Look at the thread currently below yours ( thread183-1479408 ). There's an example in it.

 

[jkirkland]

Thank you for the quick reply. I read the posts and got it to work.

For anyone whose interested, the working code is:

DECLARE @CurrentRow int
DECLARE @RowCount int, @strResult varchar(255)
DECLARE @temptable table(Id int primary key identity(1,1), HTEFund int)

INSERT INTO @temptable (HTEFund)
SELECT tblAccountMaster.GMFUND
FROM tblFundSummarySettingsA INNER JOIN tblAccountMaster ON
tblFundSummarySettingsA.GMFUND = tblAccountMaster.FSFund
GROUP BY tblFundSummarySettingsA.GMFUND, tblAccountMaster.GMFUND

SELECT @CurrentRow = 1
SELECT @RowCount=COUNT(Id) FROM @temptable
SET @strResult = ''

--For each record in @temptable xxx
WHILE @CurrentRow <= @RowCount
BEGIN

SELECT @strResult = @strResult + Cast(HTEFund As varchar(255)) + ','
FROM @temptable
WHERE id = @CurrentRow

SELECT @CurrentRow = @CurrentRow + 1
END

SELECT @strResult As BudgetedFunds

 

[gmmastros]

If you read the posts, why didn't you follow the advice in them? If you had, then you would have ended up with something like this...

[COLOR=blue]Declare[/color] @strResult [COLOR=blue]VarChar[/color](8000)

[COLOR=blue]Set[/color] @strResult = [COLOR=red]''[/color]
[COLOR=blue]SELECT[/color] @strResult = @strResult + [COLOR=#FF00FF]Convert[/color]([COLOR=blue]VarChar[/color](20), tblAccountMaster.GMFUND) + [COLOR=red]','[/color]
[COLOR=blue]FROM[/color]   tblFundSummarySettingsA [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] tblAccountMaster [COLOR=blue]ON[/color]
tblFundSummarySettingsA.GMFUND = tblAccountMaster.FSFund
[COLOR=blue]Where[/color]  tblAccountMaster.GMFUND [COLOR=blue]Is[/color] NOT NULL
[COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] tblFundSummarySettingsA.GMFUND, tblAccountMaster.GMFUND

[COLOR=blue]If[/color] [COLOR=#FF00FF]Right[/color](@StrResult, 1) = [COLOR=red]','[/color]
	[COLOR=blue]Set[/color] @strResult = [COLOR=#FF00FF]Left[/color](@strResult, Len(@strResult)-1)

[COLOR=blue]SELECT[/color] @strResult [COLOR=blue]As[/color] BudgetedFunds

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Smithsc]

Hi. I wonder if someone could help me. I am trying to run a stored procedure within MS SQL Server:

CREATE PROCEDURE SP_RVD_REPORT_01 AS

drop table tblRVDReport01

SELECT
Schedule.AAGRNUM + ' #' + Schedule.SCHEDULE_NUMBER AS Agreement_Number,
Right(Convert(VarChar(10),Schedule.SCHEDULE_START_DATE),2) + '/' + Substring(Convert(VarChar(10),Schedule.SCHEDULE_START_DATE),5,2) + '/' + Left(Convert(VarChar(10),Schedule.SCHEDULE_START_DATE),4) as Commitment_Date,
Right(Convert(VarChar(10),Schedule.SCHEDULE_START_DATE),2) + '/' + Substring(Convert(VarChar(10),Schedule.SCHEDULE_START_DATE),5,2) + '/' + Left(Convert(VarChar(10),Schedule.SCHEDULE_START_DATE),4) as Drawdown_Date,
Agreement.AAGRTYP as Facility_Type,
Asset.ASSET_TYPE_CODE as Local_Asset_Category,
Asset.IASSNUM as Unique_Asset_Identifier,
Asset.ISERNUM as Serial_Number,
Asset.DRAWDOWN_ASSET_DESCRIPTION as Asset_Description,
Asset.ASSET_DRAWDOWN_AMOUNT as Net_Asset_Cost,
Schedule.SCHEDULE_TERM as Full_Term_months,
Asset.IASSRSV as New_RV

INTO tblRVDReport01

FROM
AAGRMNT Agreement INNER JOIN ASCHEDL Schedule
ON
Agreement.AAGRNUM = Schedule.AAGRNUM INNER JOIN IASSDRW Asset
ON
(Schedule.AAGRNUM = Asset.AAGRNUM) AND
(Schedule.SCHEDULE_NUMBER = Asset.SCHEDULE_NUMBER) AND (Schedule.TERMINATION_NUMBER = Asset.TERMINATION_NUMBER)
GO


When I check the syntax it's fine but when I try to execute the script I keep getting the error message:

Error converting data type VarChar to numeric

I've looked at various websites relating to Microsoft SQL and they all say use the Convert function.

Where am I going wrong? Many thanks for any help.

 

[RyanEK]

Hi,

Another solution...

DECLARE @mystring varchar(100)
SELECT @mystring = COALESCE(@mystring + ', ', '') +  [Field] FROM [Table]
select @mystring

Ryan

 

[SQLBill]

SmithSC,

You really should create your own thread instead of 'tagging' on to someone else's. That said, the issue isn't your script - it's the data. You have a column that is VARCHAR in the table, but you are converting it to NUMERIC. That usually indicates that the column has a non-numeric value, such as a ALPHA character (ex. 1A34 will not convert).

The way to troubleshoot it is to remove the CREATE PROCEDURE line and run it as a script. When you get the error, double click on the error message and it will show you 'approximately' where the error is in the script.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLSister]

my bet woud be here
Schedule.AAGRNUM + ' #' + Schedule.SCHEDULE_NUMBER AS Agreement_Number,
are these two fields numeric datatypes or character datatatypes? Try casting them to varchar.

"NOTHING is more important in a database than integrity." ESquared

 

[RDCushing]

I agree with SQLSister: Try...

select cast(Schedule.AAGRNUM as varchar(100)) + ' #' + cast(Schedule.Schedule_Number as varchar(100)) as Agreement_Number

See what happens.


Richard D. Cushing
Sr. Consultant
SYNERGISTIC SOFTWARE SOLUTION, LLC
A Wholly-owned subsidiary of BDO Seidman, LLP