Concatenate string using a Function and a View

Question

Post reply

Concatenate string using a Function and a View

infodemers

SSCommitted

Points: 1513
More actions
May 30, 2010 at 8:23 pm

#147244

Hi,
I have the a view that returns 3677 rows of data that looks like the following as an example:
|RecNumber|Subject|
|333-123 |Canada |
|333-123 |Mexico |
|333-123 |India |
|444-456 |France |
|444-456 |Germany|
|555-882 |USA |
And I wish to concatenate the subject for each of them to make a result like this:
|RecNumber|Subject|
|333-123 |Canada,Mexico,India|
|444-456 |France,Germany|
|555-882 |USA|
I use the following Function but it is very slow.
ALTER FUNCTION [dbo].[fn_RecNumberSubject] ( @RecNo CHAR(7) )
RETURNS VARCHAR(1024)
AS BEGIN
DECLARE @ReturnValue VARCHAR(1024)
SELECT @ReturnValue = COALESCE(@ReturnValue, '') + [Subject] + ','
FROM (SELECT Recnumber, Subject
FROM View_RecNumberSubject <-- this view returns 3677 rows
WHERE Recnumber= @RecNo GROUP BY Recnumber, Subject
) AS List
RETURN Left(@ReturnValue,Len(@ReturnValue)-1)<-- to delete the coma at the end.
END
Thanks in advance!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Hi there, try this code:

SELECT p1.Recnumber,

( SELECT [Subject] + ','

FROM View_RecNumberSubject p2

WHERE p2.Recnumber = p1.Recnumber

ORDER BY COL_VAL

FOR XML PATH('')

) AS Concat_Values

FROM View_RecNumberSubject p1

GROUP BY p1.Recnumber ;

Tell us if that helped you! This solution using XML is much faster that an UDF..

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

infodemers (5/30/2010)
And I wish to concatenate the subject for each of them to make a result like this:
|RecNumber|Subject|
|333-123 |Canada,Mexico,India|
|444-456 |France,Germany|
|555-882 |USA|

Now that you have a decent answer, would you tell us why you need to denormalize the data in such a fashion?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

infodemers SSCommitted Points: 1513 More actions · Answer 3

Hi ColdCoffee,

I tried your suggestion but it ran for over 5 minutes. So I stopped it.

My solution isn't better as well.

Thanks fro trying to help me!

infodemers SSCommitted Points: 1513 More actions · Answer 4

Hi Jeff Moden ,

I need to do this because my customers want it that way in their Excel report.

Regards!

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

ColdCoffee

SSC-Dedicated

Points: 39972

May 31, 2010 at 7:48 am

#1174505

How many rows do u have you in your table?

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 6

slightly better version of your function

CREATE FUNCTION [dbo].[fn_GopiRecNumberSubject] ( @RecNo CHAR(7) )

RETURNS VARCHAR(1024)

AS BEGIN

DECLARE @ReturnValue VARCHAR(1024),@RecNo CHAR(7)

SELECT @ReturnValue = COALESCE(@ReturnValue, '') + [Subject] + ','

FROM View_RecNumberSubject WHERE Recnumber=@RecNo;

RETURN Left(@ReturnValue,Len(@ReturnValue)-1)

END

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

infodemers (5/31/2010)
Hi Jeff Moden ,
I need to do this because my customers want it that way in their Excel report.
Regards!

Heh... yeah, I kind of figured it was a customer requirement. What I was hoping for was the business logic as to why they want it that way.

Thanks anyway and thanks for the time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 8

Gopi Muluka (5/31/2010)
slightly better version of your function
CREATE FUNCTION [dbo].[fn_GopiRecNumberSubject] ( @RecNo CHAR(7) )
RETURNS VARCHAR(1024)
AS BEGIN
DECLARE @ReturnValue VARCHAR(1024),@RecNo CHAR(7)
SELECT @ReturnValue = COALESCE(@ReturnValue, '') + [Subject] + ','
FROM View_RecNumberSubject WHERE Recnumber=@RecNo;
RETURN Left(@ReturnValue,Len(@ReturnValue)-1)
END

Even though that's better, it's still a scalar function and it's still going to be relatively slow. ColdCoffee posted the XML concatenation method which is the method I would use if performance is important (and it always is for me ;-)). Try it on a million rows and see.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 9

infodemers (5/31/2010)
Hi ColdCoffee,
I tried your suggestion but it ran for over 5 minutes. So I stopped it.
My solution isn't better as well.
Thanks fro trying to help me!

I have the a view that returns 3677 rows of data that looks like the following as an example:

The problem isn't with the function in this case because even the worst scalar function will do better than that... the problem is most likely with the View itself. That's what we really need to be troubleshooting for performance.

As a sidebar, [font="Arial Black"]there's a way around this performance problem using "Divide'n'Conquer" methods[/font]. Copy the 3677 rows to a temp table using "SELECT/INTO" and then use Cold Coffee's code on that temp table. Right now, any formula used against the view seems to be causing performance problems. The view really does need repair but the work around will do just fine.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)