CREATE TABLE #EMPLOYEE(EMPID INT,NAME VARCHAR(10))

INSERT INTO #EMPLOYEE
SELECT 1,'PETER'
UNION ALL
SELECT 2,'JOHN'
UNION ALL
SELECT 3,'NINA'
UNION ALL
SELECT 4,'JOSH'
UNION ALL
SELECT 5,'CHRIS'
UNION ALL
SELECT 6,'NATHAN'
UNION ALL
SELECT 7,'RAJ'
UNION ALL
SELECT 8,'ALICE'


CREATE TABLE #JOB(JOBID INT,EMPID INT,JOBLEVEL VARCHAR(1), processed bit default(0))
INSERT INTO #JOB
SELECT 1,1,'A',0
UNION ALL
SELECT 1,2,'B',0
UNION ALL
SELECT 1,3,'B',0
UNION ALL
SELECT 2,4,'A',0
UNION ALL
SELECT 2,5,'B',0
UNION ALL
SELECT 2,6,'B',0
UNION ALL
SELECT 3,7,'B',0
UNION ALL
SELECT 3,8,'B',0


Select * from #employee
Select * from #job

Create Table #final (jobId int, A varchar(1000), B varchar(1000))

	Insert Into #final
	Select 
		jobId,
		A = (Select min(name) From #employee as z Inner Join #job as y On z.empId = y.empId Where jobId = A.jobId and processed = 0 and JobLevel = 'A'),
		B = (Select min(name) From #employee as z Inner Join #job as y On z.empId = y.empId Where jobId = A.jobId and processed = 0 and JobLevel = 'B')
	From (Select Distinct JobId From #job) as A
	
	
	Update #job
	Set
		processed = 1
	From #job
	Inner Join
		(
		Select 
			jobId,
			JobLevel = 'A',
			name = (Select min(name) From #employee as z Inner Join #job as y On z.empId = y.empId Where jobId = A.jobId and processed = 0 and JobLevel = 'A')
		From (Select Distinct JobId From #job) as A
		Union
		Select 
			jobId,
			JobLevel = 'B',
			name = (Select min(name) From #employee as z Inner Join #job as y On z.empId = y.empId Where jobId = A.jobId and processed = 0 and JobLevel = 'B')
		From (Select Distinct JobId From #job) as A
		) as Z
	On #job.jobId = z.Jobid
	and #job.empId = (Select empId From #employee Where name = z.name)
	and #job.jobLevel = z.joblevel
	and #job.processed = 0

while exists(Select 1 From #job where processed=0)
Begin
	Update #final 
	Set 
		A = isnull(A,'') + isnull(','+(Select min(name) From #employee as z Inner Join #job as y On z.empId = y.empId Where jobId = A.jobId and processed = 0 and JobLevel = 'A'),''),
		B = isnull(B,'') + isnull(','+(Select min(name) From #employee as z Inner Join #job as y On z.empId = y.empId Where jobId = A.jobId and processed = 0 and JobLevel = 'B'),'')
	From #final as A
	
	
	Update #job
	Set
		processed = 1
	From #job
	Inner Join
		(
		Select 
			jobId,
			JobLevel = 'A',
			name = (Select min(name) From #employee as z Inner Join #job as y On z.empId = y.empId Where jobId = A.jobId and processed = 0 and JobLevel = 'A')
		From (Select Distinct JobId From #job) as A
		Union
		Select 
			jobId,
			JobLevel = 'B',
			name = (Select min(name) From #employee as z Inner Join #job as y On z.empId = y.empId Where jobId = A.jobId and processed = 0 and JobLevel = 'B')
		From (Select Distinct JobId From #job) as A
		) as Z
	On #job.jobId = z.Jobid
	and #job.empId = (Select empId From #employee Where name = z.name)
	and #job.jobLevel = z.joblevel
	and #job.processed = 0
End

Select * From #final

DROP TABLE #EMPLOYEE
DROP TABLE #JOB
Drop Table #final

...
End

Update #final
Set 
	A = case when charindex(',',B)>0 then left(B,charindex(',',B)-1) else B end,
	B = case when charindex(',',B)>0 then right(B,len(B) - charindex(',',B)) else null end
From #final
Where A = ''

Select * From #final
...

CREATE FUNCTION f_emps (@JOBID int) RETURNS Varchar(2000)
AS
BEGIN
DECLARE @Emplist varchar(2000)

	SELECT @Emplist = CASE WHEN @Emplist IS NULL THEN NAME + ' ' ELSE @Emplist + ',' + NAME  End 
	FROM EMPLOYEE, JOB
	WHERE @JOBID = JOB.JOBID
	AND JOB.EMPID = EMPLOYEE.EMPID
	ORDER BY JOB.JOBLEVEL, EMPLOYEE.NAME

RETURN Replace(@Emplist,' ,',' ')
END

SELECT JOBID, Cast (Substring(AB,1,Charindex(' ',AB)) AS Varchar(10)) A, Rtrim(Substring(AB,Charindex(' ',AB) + 1 ,255)) B 
FROM (
	SELECT JOBID, dbo.f_emps(JOBID) AB
	FROM JOB
	GROUP BY JOBID
     ) x

JOBID       A          B            
----------- ---------- -------------
1           PETER      JOHN,NINA
2           JOSH       CHRIS,NATHAN
3           ALICE      RAJ

Or if you don't need to seperate names: 

SELECT JOBID, dbo.f_emps(JOBID) AB
FROM JOB
GROUP BY JOBID

JOBID       AB             
----------- ----------------
1           PETER JOHN,NINA
2           JOSH CHRIS,NATHAN
3           ALICE RAJ

SELECT JOBID, Cast (NAME AS VARCHAR(255)) NAME
INTO #workingtable
FROM EMPLOYEE, JOB
WHERE JOB.EMPID = EMPLOYEE.EMPID
ORDER BY JOB.JOBID DESC, JOB.JOBLEVEL, EMPLOYEE.NAME


DECLARE @jobid int
DECLARE @name varchar(255)

UPDATE #workingtable
SET @Name = NAME = Case When @jobid = Jobid Then @name + ',' + NAME Else NAME End,
@jobid = jobid

SELECT JOBID, Cast (Substring(AB,1,Charindex(',',AB) - 1) AS Varchar(10)) A, Rtrim(Substring(AB,Charindex(',',AB) + 1 ,255)) B 
FROM (
	select Jobid, Max(name) AB
	from #workingtable
	group by jobid
     ) x


DROP TABLE #workingtable

JOBID       A          B         
----------- ---------- ------------
1           PETER      JOHN,NINA
2           JOSH       CHRIS,NATHAN
3           ALICE      RAJ

quote:

---

Originally posted by Seventhnight
- what would happen if the data was not listed in the correct order (ie 'B' before 'A')
for 'A'

---

quote:

---

Originally posted by Seventhnight
- what would happen if more than one entry was listed for level 'A'
for 'A'

---

quote:

---

Originally posted by Seventhnight
- what would happen if there is only 1 person list for 'B' and none for 'A'

---

quote:

---

Originally posted by Seventhnight

Ken - Thanks for the vote of confidence!
Corey

---

quote:

---

...because Corey is usually right on the money...

---