可以使用PIVOT表(使用sql Server)完成此操作:
SELECT user_name, [dept1name], [dept2name], [dept3name], ...
FROM
(SELECT s.user_name, d.dept_name,
case when sd.user_no is not null then 'X' else '' end as matches
from staff s
cross join department d
left join staff_dept sd on s.user_no = sd.user_no and d.dept_no = sd.dept_no
) AS s
PIVOT
(
min(matches)
FOR dept_name IN ([dept1name], [dept2name], [dept3name], ...)
) AS pvt
order by user_name
演示:http ://www.sqlfiddle.com/#!3/c136d/5