Concatenate string - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Concatenate string

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2010-05-29 : 16:49:02

Sachin.Nand

2937 Posts

Posted - 2010-05-29 : 16:55:50

Try this


declare @tbl as table(num int,country varchar(40))
insert into @tbl
select 1,'canada' union all
select 1,'Mexico' union all
select 5,'Canada' union all
select 5,'Mexico'

select num,stuff((select  ','+country   from @tbl t1 group by country for XML path('')),1,1,'')from @tbl t2
group by num

If this also does not help then maybe you will have explain more about your table,data and the indexes on it.

PBUH

infodemers
Posting Yak Master

183 Posts

Posted - 2010-05-29 : 23:46:34

Hi,

I cannot use variable or temporary tables into a view.

Thanks anyway Idera!

quote:
Originally posted by Idera

Try this

declare @tbl as table(num int,country varchar(40))
insert into @tbl
select 1,'canada' union all
select 1,'Mexico' union all
select 5,'Canada' union all
select 5,'Mexico'

select num,stuff((select ','+country from @tbl t1 group by country for XML path('')),1,1,'')from @tbl t2
group by num

If this also does not help then maybe you will have explain more about your table,data and the indexes on it.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-05-30 : 02:46:51

No there is no need for temporary table.The temporary table is meant just to show sample data.
You can modify your function to this


ALTER FUNCTION [dbo].[fn_RecNumberSubject] ( @RecNo CHAR(7) )
RETURNS VARCHAR(1024)

AS BEGIN

DECLARE @ReturnValue VARCHAR(1024)

Select @ReturnValue=stuff((select  ','+ Subject from yourview vw1
 where vw1.RecNumber=vw2.RecNumber and vw1.RecNumber=@RecNo
 group by Subject for XML path('') ),1,1,'')from yourview vw2 group by RecNumber

RETURN @ReturnValue

PBUH

infodemers
Posting Yak Master

183 Posts

Posted - 2010-05-30 : 21:53:07

Hi Idera,

I tried your suggestion, but unfortunately it takes over 5 minutes to run, so I stopped it. Mine isn't better as well. I ran the estimated execution plan and what cost the most in the execution is the clustered index seek and index seek.
The view takes only 8 seconds to show all the 3677 rows. And then I run the Function and it runs for many many minutes...Bizarre.

Thanks for trying to help Idera!

quote:
Originally posted by Idera

No there is no need for temporary table.The temporary table is meant just to show sample data.
You can modify your function to this

ALTER FUNCTION [dbo].[fn_RecNumberSubject] ( @RecNo CHAR(7) )
RETURNS VARCHAR(1024)

AS BEGIN

DECLARE @ReturnValue VARCHAR(1024)

Select @ReturnValue=stuff((select ','+ Subject from yourview vw1
where vw1.RecNumber=vw2.RecNumber and vw1.RecNumber=@RecNo
group by Subject for XML path('') ),1,1,'')from yourview vw2 group by RecNumber

RETURN @ReturnValue

PBUH

Kristen
Test

22859 Posts

Posted - 2010-05-31 : 02:58:39

"it takes over 5 minutes to run"

How are you applying the Function?

SELECT RecNumber, dbo.fn_RecNumberSubject(RecNumber)
FROM MyTable

which will be slow, or using CROSS APPLY (which should be faster)

Dunno if it will make any difference, but might be worth trying these alterations


DECLARE @ReturnValue VARCHAR(1024)

	SELECT	@ReturnValue = COALESCE(@ReturnValue+',', '') + [Subject]
	FROM
	(
		SELECT	Recnumber, Subject
		FROM	dbo.View_RecNumberSubject 
		WHERE	Recnumber= @RecNo 
		GROUP BY Recnumber, Subject
	) AS List 
	RETURN @ReturnValue

Not sure why you are using GROUP BY or derived table?


	SELECT	@ReturnValue = COALESCE(@ReturnValue+',', '') + [Subject]
	FROM	dbo.View_RecNumberSubject 
	WHERE	Recnumber= @RecNo
	ORDER BY [Subject]
	RETURN @ReturnValue

infodemers
Posting Yak Master

183 Posts

Posted - 2010-05-31 : 09:53:39

Hi Kristen,

I apply the function like this, from a view instead of a table:

SELECT RecNumber, dbo.fn_RecNumberSubject(RecNumber)
FROM MyView

I haven't had a chance to try your suggestions, but I will soon...

Thanks!

quote:
Originally posted by Kristen

"it takes over 5 minutes to run"

How are you applying the Function?

SELECT RecNumber, dbo.fn_RecNumberSubject(RecNumber)
FROM MyTable

which will be slow, or using CROSS APPLY (which should be faster)

Dunno if it will make any difference, but might be worth trying these alterations

DECLARE @ReturnValue VARCHAR(1024)

SELECT @ReturnValue = COALESCE(@ReturnValue+',', '') + [Subject]
FROM
(
SELECT Recnumber, Subject
FROM dbo.View_RecNumberSubject
WHERE Recnumber= @RecNo
GROUP BY Recnumber, Subject
) AS List
RETURN @ReturnValue

Not sure why you are using GROUP BY or derived table?

SELECT @ReturnValue = COALESCE(@ReturnValue+',', '') + [Subject]
FROM dbo.View_RecNumberSubject
WHERE Recnumber= @RecNo
ORDER BY [Subject]
RETURN @ReturnValue

Kristen
Test

22859 Posts

Posted - 2010-05-31 : 12:43:02

Other problem I perceive:

You say "FROM View_RecNumberSubject <-- this view returns 3677 rows" is for a single value of @RecNo? if so, and given that the concatenation buffer is only VARCHAR(1024) and each value is length of [Subject] - say 10 characters average, thus buffer has a max of 100 entries, probably less, then the View must be returning many duplicate values, by using that view, and that will be hopelessly inefficient.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-01 : 02:11:12

There is also a Quirky update technique that you may need to play with
See the last example
http://beyondrelational.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources