SELECT
  SR.RoleName,
  SR.RoleDescription,
  IF((SELECT COUNT(*) FROM taRoleRightLink AS X WHERE (X.RoleName = SR.RoleName) AND (X.RightName = 'srUseCMS'     )) > 0, 1, 0) AS UseCMS,
  IF((SELECT COUNT(*) FROM taRoleRightLink AS X WHERE (X.RoleName = SR.RoleName) AND (X.RightName = 'srClientClerk')) > 0, 1, 0) AS ClientClerk,
  IF((SELECT COUNT(*) FROM taRoleRightLink AS X WHERE (X.RoleName = SR.RoleName) AND (X.RightName = 'srMDAuthorise')) > 0, 1, 0) AS MDAuthorise,
  IF((SELECT COUNT(*) FROM taRoleRightLink AS X WHERE (X.RoleName = SR.RoleName) AND (X.RightName = 'srAppmtAdmin' )) > 0, 1, 0) AS AppmtAdmin,
  IF((SELECT COUNT(*) FROM taRoleRightLink AS X WHERE (X.RoleName = SR.RoleName) AND (X.RightName = 'srCaseAdmin'  )) > 0, 1, 0) AS CaseAdmin,
  IF((SELECT COUNT(*) FROM taRoleRightLink AS X WHERE (X.RoleName = SR.RoleName) AND (X.RightName = 'srClientAdmin')) > 0, 1, 0) AS ClientAdmin,
  IF((SELECT COUNT(*) FROM taRoleRightLink AS X WHERE (X.RoleName = SR.RoleName) AND (X.RightName = 'srSystemAdmin')) > 0, 1, 0) AS SystemAdmin,
  IF((SELECT COUNT(*) FROM taRoleRightLink AS X WHERE (X.RoleName = SR.RoleName) AND (X.RightName = 'srUserAdmin'  )) > 0, 1, 0) AS UserAdmin
FROM
  taSystemRole AS SR
ORDER BY
  SR.RoleOrder;