concatenate subsquery result set

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

SQL Server 2005 Forums

Transact-SQL (2005)

concatenate subsquery result set

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2008-11-11 : 17:39:02

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

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-11 : 17:56:40

[code]
select distinct
emp.id, emp.ssn, emp.dob, STUFF(
(select ','+emp_typ_desc from employeedesc where empid = emp.id for XML Path(''))
),1,1,'') as emp_typ_desc
from employee emp
where (convert(varchar,emp.dob,101) >= '11/10/2008')
AND (convert(varchar,emp.dob,101) <= '11/17/2008')
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-12 : 02:10:49

quote:

Originally posted by hanbingl


select distinct 
emp.id, emp.ssn, emp.dob, STUFF(
(select ','+emp_typ_desc from employeedesc where empid = emp.id for XML Path(''))
),1,1,'') as emp_typ_desc
from employee emp
where (convert(varchar,emp.dob,101) >= '11/10/2008')
AND (convert(varchar,emp.dob,101) <= '11/17/2008')

Dont convert dates to varchars. Work on dates only

select distinct
emp.id, emp.ssn, emp.dob, STUFF(
(select ','+emp_typ_desc from employeedesc where empid = emp.id for XML Path(''))
),1,1,'') as emp_typ_desc
from employee emp
where emp.dob >= '20081110')
AND emp.dob < '20081118')

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources