Hi
I tried in sql server using command object here is the query
Select
Jb.ID as 'Job Id',
JB.Name as 'Job Name',
emp1.ID as 'JobCode1',
emp1.NAME as 'JobName1',
emp2.ID as 'JobCode2',
emp2.NAME as 'JobName2',
EMP3.ID as 'JobCode3',
emp3.NAME as 'JobName3'
from JOBCODE jb
left join EMPLOYEE emp1 on jb.ID= emp1.JOBCODE1
left join EMPLOYEE emp2 on jb.ID=emp2.JOBCODE2
left join EMPLOYEE emp3 on jb.ID=emp3.JOBCODE3
Then group by Job Id
Then place Job Name field in Group Header
then create 3 details section for Jobcode 1, Jobcode2,Jobcode 3 Place the fields accordingly in them and suppress blank sections.
Output
1 Manager
1000 Bob
2 Cook
1002 Chris
1000 Bob
3 Server
1001 Jim
1002 Chris
4 Diswasher
The only issue i have is not able to sort details for ID 2 COOK
Cheers
Rahul