November 30, 2005 at 8:19 am
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???
csullivan
December 1, 2005 at 7:31 am
drop table #result
drop table #Employee
drop table #Empdept
drop table #Department
create table #result(emp_id int, all_dept_desc varchar(500))
create table #Employee(emp_id int, emp_name varchar(10))
insert into #Employee(emp_id, emp_name) values (1, 'John')
insert into #Employee(emp_id, emp_name) values (2, 'Mary')
insert into #Employee(emp_id, emp_name) values (3, 'Jo')
create table #Empdept(emp_id int, dept_id int)
insert into #Empdept(emp_id, dept_id) values (1, 10)
insert into #Empdept(emp_id, dept_id) values (1, 20)
insert into #Empdept(emp_id, dept_id) values (1, 30)
insert into #Empdept(emp_id, dept_id) values (2, 10)
insert into #Empdept(emp_id, dept_id) values (2, 40)
insert into #Empdept(emp_id, dept_id) values (3, 30)
insert into #Empdept(emp_id, dept_id) values (3, 50)
create table #Department(dept_id int, dept_desc varchar(20))
insert into #Department(dept_id, dept_desc) values (10, 'sales')
insert into #Department(dept_id, dept_desc) values (20, 'marketing')
insert into #Department(dept_id, dept_desc) values (30, 'finance')
insert into #Department(dept_id, dept_desc) values (40, 'operations')
insert into #Department(dept_id, dept_desc) values (50, 'IT')
declare @deptNM varchar(500)
declare @DeptDesc varchar(20)
declare @EmpID int
declare curEmp cursor for
select emp_id
from #Employee
order by emp_id
open curEmp
fetch next from curEmp into @EmpID
while @@fetch_status = 0
begin
set @deptNM = ''
declare curDept cursor for
select dept.dept_desc
from #Empdept as emp
join #Department as dept
on (emp.dept_id = dept.dept_id)
where emp.emp_id = @EmpID
order by dept.dept_desc
open curDept
fetch next from curDept into @DeptDesc
while @@fetch_status = 0
begin
if @deptNM = ''
begin
set @deptNM = @DeptDesc
end
else
begin
set @deptNM = @deptNM + ', ' + @DeptDesc
end
fetch next from curDept into @DeptDesc
end
DEALLOCATE curDept
insert into #result(emp_id, all_dept_desc)
select @EmpID, @deptNM
fetch next from curEmp into @EmpID
end
DEALLOCATE curEmp
select * from #result
December 1, 2005 at 9:41 am
Here's a variation of the above reply that uses a loop instead of a cursor:
drop table Employee
drop table EmpDept
drop table Department
go
SET NOCOUNT ON
create table Employee(emp_id int, emp_name varchar(10))
insert into Employee(emp_id, emp_name) values (1, 'John')
insert into Employee(emp_id, emp_name) values (2, 'Mary')
insert into Employee(emp_id, emp_name) values (3, 'Jo')
create table Empdept(emp_id int, dept_id int)
insert into Empdept(emp_id, dept_id) values (1, 10)
insert into Empdept(emp_id, dept_id) values (1, 20)
insert into Empdept(emp_id, dept_id) values (1, 30)
insert into Empdept(emp_id, dept_id) values (2, 10)
insert into Empdept(emp_id, dept_id) values (2, 40)
insert into Empdept(emp_id, dept_id) values (3, 30)
insert into Empdept(emp_id, dept_id) values (3, 50)
create table Department(dept_id int, dept_desc varchar(20))
insert into Department(dept_id, dept_desc) values (10, 'sales')
insert into Department(dept_id, dept_desc) values (20, 'marketing')
insert into Department(dept_id, dept_desc) values (30, 'finance')
insert into Department(dept_id, dept_desc) values (40, 'operations')
insert into Department(dept_id, dept_desc) values (50, 'IT')
---------------------------------------------------------------------
DECLARE @emp_id int
, @allDesc varchar(200)
CREATE TABLE #results
(
emp_id int
, allDesc varchar(200)
)
SELECT @emp_id = Min(e.emp_id)
FROM Employee e
JOIN EmpDept ed
ON e.emp_id = ed.emp_id
JOIN Department d
ON ed.dept_id = d.dept_id
SET @allDesc = ''
WHILE @emp_id IS NOT NULL
BEGIN
SELECT @allDesc = @allDesc + d.dept_desc + ','
FROM Employee e
JOIN EmpDept ed
ON e.emp_id = ed.emp_id
JOIN Department d
ON ed.dept_id = d.dept_id
WHERE e.emp_id = @emp_id
ORDER BY e.emp_id, d.dept_desc
IF Right(@allDesc,1) = ','
SET @allDesc = Left(@allDesc, Len(@allDesc) - 1)
INSERT #Results (emp_id, allDesc) VALUES (@emp_id, @allDesc)
-- get next emp_id
SELECT @emp_id = Min(e.emp_id)
FROM Employee e
JOIN EmpDept ed
ON e.emp_id = ed.emp_id
JOIN Department d
ON ed.dept_id = d.dept_id
WHERE e.emp_id > @emp_id
END -- WHILE
SET NOCOUNT OFF
SELECT * FROM #Results
DROP TABLE #Results
December 2, 2005 at 9:12 am
This was a ONCE off report reqd by OPS
csullivan
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply