Combine "Concat" results of a query is it possible?

Question

Post reply

Combine "Concat" results of a query is it possible?

Rick Musa

Old Hand

Points: 374
More actions
May 31, 2006 at 7:25 am

#94251

select vdn from Table where type = '2B' and callcenter = 'Tulsa'
results are:
VDN
1032
1532
The result I am looking for is
VDN
1032, 1532

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

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

brij_rauthan SSCommitted Points: 1521 More actions · Answer 1

Declare @VDNLIST Varchar(4000)

SELECT @VDNLIST = COALESCE(@VDNLIST + ', ', '') + vdn as 'VDN' FROM Table

WHERE type = '2B' and callcenter = 'Tulsa'

SELECT @VDNLIST

Brij

Rick Musa Old Hand Points: 374 More actions · Answer 2

Declare @VDNLIST varchar(4000)

SELECT @VDNLIST = COALESCE(@VDNLIST + ',','' ) + Cast (vdn as varchar(5))

FROM Rpm_Maintenance..vdn_partners AS a

WHERE type = '2b' and callcenter = 'Fredericton'

select @VDNLIST

This works great, thought all would go smoothly if I could figure that part out. Now I want to add more fields.

Declare @VDNLIST varchar(4000)

SELECT Type, Callcenter, @VDNLIST = COALESCE(@VDNLIST + ',','' ) + Cast (vdn as varchar(5))

FROM Rpm_Maintenance..vdn_partners AS a

WHERE type = '2b' and callcenter = 'Fredericton'

select @VDNLIST

the error that I get is.

Server: Msg 141, Level 15, State 1, Line 5

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Marshall Smith SSCrazy Points: 2041 More actions · Answer 3

Put your concatenation process in a UDF, passing whatever value you're grouping on. So, in your case, it might look like:

CREATE FUNCTION ConcatVDN (@type varchar(30), @center varchar(50))

RETURNS varchar(4000)

AS

DECLARE @VDNLIST varchar(4000)

SET @VDNLIST = ''

SELECT @VDNLIST = COALESCE(@VDNLIST + ',','') + CAST(vdn as varchar(5))

FROM Rpm_Maintenance..vdn_partners

WHERE type = @type AND callcenter = @center

RETURN @VDNLIST

Then, in your regular query,

SELECT Type, Callcenter, ConcatVDN(Type, Callcenter) AS VDNList

FROM Rpm_maintenance..vdn_partners

GROUP BY Type, Callcenter

Rick Musa Old Hand Points: 374 More actions · Answer 4

Thanks - Sounds good, I created the function 'successfully' I think, now when I run my query

CREATE FUNCTION ConcatVDN (@type varchar(2), @center varchar(25))

RETURNS varchar(4000)

AS

BEGIN

DECLARE @VDNLIST varchar(4000)

SET @VDNLIST = ''

SELECT @VDNLIST = COALESCE(@VDNLIST + ',','') + CAST(vdn as varchar(5))

FROM Rpm_Maintenance..vdn_partners

WHERE type = @type AND callcenter = @center

RETURN @VDNLIST

END

<<<Query>>>>

SELECT Type, Callcenter, ConcatVDN(Type, Callcenter) AS VDNList

FROM Rpm_maintenance..vdn_partners

GROUP BY Type, Callcenter

I get:

Server: Msg 195, Level 15, State 10, Line 1

'ConcatVDN' is not a recognized function name.

Rick Musa Old Hand Points: 374 More actions · Answer 5

Never mind I forgot the dbo. on my select.

Thanks for all your help

R.

Rick Musa Old Hand Points: 374 More actions · Answer 6

Just about complete - Now I have a proceeding comma before in the VNDList

The result that I get now after creating the the function.

Type callcenter VDNList

1B Fredericton ,1034,1534

Marshall Smith SSCrazy Points: 2041 More actions · Answer 7

Grr. That's a result of combining my methods and brij's.

Remove the SET @VDNList = '' statement from your function. That should mean that the variable is initialized as NULL, and so the COALESCE statement will mean that you don't get the initial comma. My approach, typically, is to SET the variable to the empty string (long hours of having it beaten into me to explicitly initialize all variables), and then strip the final comma. I suppose you could also set it up with a CASE statement to check and see if the variable is currently the empty string.

brij_rauthan SSCommitted Points: 1521 More actions · Answer 8

instead do SET @VDNList = null.

this will also not add comma in front

Brij

Rick Musa Old Hand Points: 374 More actions · Answer 9

Of course! can't believe I missed that completely - All is working Great! Thanks so much.

Rick