I've been working on this for a week but have yet to find a solution. I'm losing hair over this. Any & all help would be appreciated. Thanks a lot.

1. Using SQL Server 2000.
2. Need to combine multiple rows in query's result to 1 row and need to do this for more than 30 tables/fields. If possible, need to do this using function / stored procedure.
3. I've written a function but there's an error and I'm can't find a workaround. The function is:
-----------------------------------------
create function [dbo].[MergeRows] (
@query varchar(5000),
@field varchar(50),
@separator varchar(10)
)
returns varchar(4000) as
begin

declare @exec as nvarchar(4000)
declare @retval as nvarchar(4000)
declare @result as nvarchar(4000)

set @exec = 'begin '
set @exec = @exec + 'declare @return as varchar(4000) '
set @exec = @exec + 'select @return = coalesce(@return + ''' + @separator + ''', '''') + cast(' + @field + ' as varchar(1000)) '
set @exec = @exec + 'from (' + @query + ')q1 order by ' + @field + ' '
set @exec = @exec + 'select @return end'

exec sp_executesql @exec, N'@result nvarchar(4000) output', @result = @retval output

return @retval

end
-----------------------------------------
4. I used the following to test:
select dbo.mergerows('select accountname from account', 'accountname', ';')
and the error is:
Only functions and extended stored procedures can be executed from within a function.

Thanks.

the straightforward answer to this is that SQL (the language) is very poor at producing this sort of result set, all for good reason that is not worth expanding here.

If this resultset is consumed by a reporting tool of some sort (reporting services, Access, Excel, ASP.NET etc.) then it will be far easier to return a table of data to that and do your row merging there. RDBMSs excel at storing and retrieving data. Reporting tools excel at manipulating and formatting datasets.

Hi

What you are looking for is crosstab report which converts rows to columns. MS SQL 2005 introduced PIVOT but in your case it may not work properly as you need to dispaly unknown values which comes from a 3rd table. For your convenience, this is URL that you can refer to Stored procedure: A simple way to perform crosstab operations

Once you download the sp_CrossTab (at the bottom of the page), put it in the Master database. Then use the following script to test it.

IF OBJECT_ID('tempdb..#account') IS NOT NULL
drop table #account

create table #account
(
id bigint identity(1,1),
AccountName varchar(50),
PurchaseDate datetime not null,
PurchaseAmount float not null
)

insert #account Values('Bill Gates', '2009-01-11', 300)
insert #account Values('David Jung', '2009-01-11', 100)
insert #account Values('Steve Jobs', '2009-01-11', 200)
insert #account Values('Tom Hanks', '2009-01-21', 300)
insert #account Values('Julia Roberts', '2009-01-21', 100)
insert #account Values('Mel Gibson', '2009-01-21', 200)

EXECUTE sp_Crosstab
'select AccountName,PurchaseAmount,datepart(year,PurchaseD ate) [PurcharYear] from #account'
,NULL
,NULL
,'AccountName'
,'PurchaseAmount'
,'sum'

It may not directly give you the solution but still it may give you something to try, I hope.

Tell me about it I learned the hard way when a varchar variable exceeded 512KB in a SELECT @var=@var+col FROM SomeTable which resulted in a massive attack on tempdb due to a threshold on variables. If you thought sort and hash spills to tempdb was bad, wait 'till you see what happens when a variable exceeds 512KB.

Do what Pottle suggests and move your string manipulation to the presentation layer.