Comma Seperated List as a Column - Function help

[mossbs]

Hi guys,

I need to produce a report that in one columns has all the market codes as a comma seperated list - for this I have edited a UDF (function) I already had which uses COALESCE to produce the comma seperated list...

ALTER function [dbo].[udf_List_Markets] 
(@Id int)
returns nvarchar(1000) 
as

begin
   declare @Markets nvarchar(1000);

   select @Markets = COALESCE(@Markets + ', ', '') + LM.sId_DSysMkt
   from GM 
	inner join LM ON LM.nId_GM = GM.nId 
   where GM.nId_DPblItm = @Id
   order by LM.sId_DSysMkt 

   return @Markets
end

... I call this as part of the select list and it works fine if only selecting a few records - however the report will produce hundreds of thousand - possibly over a million rows.

This means it is taking a ridiculous amount of time to run as it has to call this each time...

Is there another way of doing this I'm not seeing that will not take as long?

Cheers in advance,

Dan

 

[imex]

Hi,

Try a test with the method used in the topic below:

http://www.tek-tips.com/viewthread.cfm?qid=1691180

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[TheBugSlayer]

You can build on this example that I just posted in another thread:

select REPLACE(
	( 
        SELECT 
            REPLACE(Email, CHAR(13), '') = '*' AS Email
        FROM 
            dbo.SOWEmailManagersHistory 
        FOR XML PATH ('')
    ), 
	'*', 
	', ')

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[mossbs]

Cheers guys - I've tried the STUFF method - but still takes ages...
Will try the REPLACE method see how this fairs.

Got a feeling the slow run time may just be the sheer amount of data...