MULTIPLE SELECT
-
Hi Everyone
I have 3 tables
Employee(emp_id emp_name)
example of data
1, John
2, Mary
3, Jo
Empdept(emp_id, dept_id)
1, 10
1, 20
1, 30
2, 10
2, 40
3, 30
3, 50
Department(dept_id, dept_desc)
10, sales
20, marketing
30, finance
40, operations
50, IT
I need to get a list of emp_id, dept_desc
But I need it to be on one row
Like
1,sales,marketing,finance
2, sales,operations
3, finance, IT
Can anyone help with this pls???
Kindest Regards,csullivan
-
The usual way is to create a function to concatenate the output and then
apply it to the source table. This is a lot better handled in the client side but if you still want to do it on the server here you go:
create function dbo.concat_Dept ( @empid int)
returns varchar(4000)
as
declare @local varchar(4000)
begin
select @local = isnull( @local +', ', '') + dept_desc
from Empdept e join Depeartment d on e.dept_id = d.dept_id
where e.emp_id = @empid
returns @local
end
-- then run something like this:
select emp_id, emp_name, dbo.concat_Dept( emp_id) as Departments
from employee
* Noel -
ciara - please do not cross post...noel - your friend has responded to the other thread
- as usual the comments are a "must read" - 
**ASCII stupid question, get a stupid ANSI !!!** -
Sushila, now I am feeling hurt
..."my friend" ??? .. THAT, is too painful to bearAnd you are correct as usual A LOT of bashing and a little help
* Noel -
lo siento...should have put it in quotes and told you that it was "tongue in cheek"..
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply