Concatenate subsquery result set

Question

Concatenate subsquery result set

am-244616

SSCarpal Tunnel

Points: 4936
More actions
November 11, 2008 at 3:38 pm

#383354

Guys,
I have scenario where the subquery is failing because it is returning multiple values.
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.
Below is the query
select emp.id, emp.ssn, emp.dob, emp_typ_desc = (select emp_typ_desc from employeedesc where empid = emp.id)
from employee emp
where (convert(varchar,emp.dob,101) >= '11/10/2008')
AND (convert(varchar,emp.dob,101) <= '11/17/2008')
Is there any I can concatenate multiple values emp_typ_desc for each empid into one string. I have to use coalesce with vaiable
in function which takes empid as input but the performance deteriorates when using the function instead of subquery
ALTER FUNCTION [dbo].[Getempdesc](@empId int)
RETURNS VARCHAR(1000) AS
BEGIN
DECLARE @emp_typ_desc varchar(1000)
SELECT @emp_typ_desc = COALESCE( @emp_typ_desc + ', ', '') +
CAST(emp_typ_desc AS VARCHAR(25))
FROM employeedesc d
INNER JOIN employee e on d.empid = e.empid
WHERE e.empid =@empid
RETURN @emp_typ_desc
END
Any suggestions and inputs to concatenate results into a string in the substring would help
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Here is an article by some smart guy that explains how to do this: http://www.sqlservercentral.com/articles/Test+Data/61572/

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

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

Never heard of him... is he new?

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Nah, just one of the usual suspects.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Michelle Ufford-459603 SSCommitted Points: 1642 More actions · Answer 4

There's a couple of different ways you can handle this. Here's one way:

WITH myCTE

AS

(

SELECT empid

, REPLACE( REPLACE( REPLACE(

( SELECT emp_typ_desc

FROM employeedesc As ed1 WITH (NOLOCK)

WHERE ed1.empid = ed2.empid

ORDER BY emp_typ_desc

FOR XML RAW)

, '"/><row emp_typ_desc="', ', ')

, '<row emp_typ_desc="', '')

, '"/>', '')

AS 'emp_typ_desc'

FROM employeedesc AS ed2 WITH (NOLOCK)

GROUP BY empid

)

SELECT emp.id, emp.ssn, emp.dob, cte.emp_typ_desc

FROM employee emp

LEFT JOIN myCTE cte

ON emp.id = cte.empid

WHERE (CONVERT(varchar,emp.dob,101) >= '11/10/2008')

AND (CONVERT(varchar,emp.dob,101) <= '11/17/2008');

HTH!

Regards,

Michelle Ufford
SQLFool.com - Adventures in SQL Tuning