Concatenating Row Values Into One Row/Valu

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

SQL Server 2005 Forums

Transact-SQL (2005)

Concatenating Row Values Into One Row/Valu

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-03-31 : 11:47:14

Can anyone help me out with the following function?

Basically, I want to pass a value to it. That value is then used to return all rows from a table based on the value but as one concatinated string/row.

When I run the below query, I get the last value in the row set.

Hope this makes sense.

ALTER FUNCTION [dbo].[fn_ReturnBranches](@mid VARCHAR(3))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @v VARCHAR(8000)
SELECT @v = ''
SELECT @v = @v + co.symbol + ', '
FROM linkserver.aaa.dbo.company co
WHERE (co.home_office_symbol = @mid and co.status = 'Active')
IF len(@v ) > 0
SELECT @v = left(@v, len(@v)-1)
RETURN @v
END

Calling function:

Select 'Branches' = dbo.fn_ReturnBranches('21')
from linkserver.aaa.dbo.company

Jazid
Starting Member

3 Posts

Posted - 2010-03-31 : 12:56:43

Look ok to me.
What happens if you run this locally on the [linkedserver] server (asssuming that it is a sql server)?

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-03-31 : 13:06:42

Hi Jazid,

Weird...., it works fine when runing direcly on the Linked Server.

Only has an issue when running from another database/server referencing the Linked Server.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-03-31 : 13:23:30

This worked in place of the linked server:

ALTER FUNCTION [dbo].[fn_ReturnBranches](@mid VARCHAR(3))
RETURNS VARCHAR(8000)
AS
BEGIN

DECLARE @TempQ TABLE (symbol VARCHAR(3))
DECLARE @concatSymbols AS VARCHAR(MAX)

INSERT INTO @TempQ (symbol)
SELECT c.symbol FROM lsname.dbname.dbo.company c
WHERE c.other_symbol = @mid and c.status = 'Active'

SET @concatSymbols = null
SELECT @concatSymbols = coalesce(@concatSymbols + ', ' + symbol, symbol)
FROM @TempQ

-- return list of symbols
RETURN @concatSymbols

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources