SQL Question

AND ((emp.JobDescr LIKE 'TEACHER MS 187 DAYS')
                     OR (emp.JobDescr LIKE 'TEACHER ES 187 DAYS')
                     OR (emp.JobDescr LIKE 'ADM ASST ES PG C5 @ 200 DAYS')
                     OR (emp.JobDescr LIKE 'CN MANAGER MS PG4 @ 178 DAYS')
                     OR (emp.JobDescr LIKE 'COUNSELOR MS HS OR ES PG2')...

Select allOpen in new window

AND (emp.JobDescr in ( 'TEACHER MS 187 DAYS', 
                     'TEACHER ES 187 DAYS',
                    'ADM ASST ES PG C5 @ 200 DAYS',
                    'CN MANAGER MS PG4 @ 178 DAYS',
                    'COUNSELOR MS HS OR ES PG2'...

Select allOpen in new window

SELECT DISTINCT 
                      emp.UniqueID AS 'EmployeeID', 'b' + RIGHT('00000' + CONVERT(varchar, emp.UniqueID), 5) AS 'Username', munispre.prem_lname AS 'LastName', 
                      munispre.prem_fname AS 'FirstName', munispre.prem_minit AS 'MiddleName', munispre.prem_hire AS 'HireDate', emp.Email, 
                      munispre.prem_act_stat AS 'ActiveAccount', '' AS 'GroupTypeID', CASE WHEN emp.JobDescr LIKE 'TEACHER MS 187 DAYS' THEN REPLACE(emp.JobDescr, 
                      '187 DAYS', '') WHEN emp.JobDescr LIKE 'TEACHER ES 187 DAYS' THEN REPLACE(emp.JobDescr, '187 DAYS', '') 
                      WHEN emp.JobDescr LIKE 'ADM ASST ES PG C5 @ 200 DAYS' THEN REPLACE(emp.JobDescr, 'PG C5 @ 200 DAYS', '') 
                      WHEN emp.JobDescr LIKE 'CN MANAGER MS PG4 @ 178 DAYS' THEN REPLACE(emp.JobDescr, 'PG4 @ 178 DAYS', '') 
                      WHEN emp.JobDescr LIKE 'COUNSELOR MS HS OR ES PG2' THEN REPLACE(emp.JobDescr, 'PG2', '') 
                      WHEN emp.JobDescr LIKE 'TEACHER HS 187 DAYS' THEN REPLACE(emp.JobDescr, '187 DAYS', '') 
                      WHEN emp.JobDescr LIKE 'ADM ASST MS PG C5 @ 205 DAYS' THEN REPLACE(emp.JobDescr, 'PG C5 @ 205 DAYS', '') 
                      WHEN emp.JobDescr LIKE 'HEAD CUST MS W/HVAC PG 4 @ 248' THEN REPLACE(emp.JobDescr, 'PG 4 @ 248', '') 
                      WHEN emp.JobDescr LIKE 'ASSISTANT PRIN  ES PG3' THEN REPLACE(emp.JobDescr, 'PG3', '') 
                      WHEN emp.JobDescr LIKE 'ATTEND CK MS PG C3 @ 200 DAYS' THEN REPLACE(emp.JobDescr, 'PG C3 @ 200 DAYS', '') 
                      WHEN emp.JobDescr LIKE 'HEAD CUST ES W/HVAC PG 3 @ 248' THEN REPLACE(emp.JobDescr, 'PG 3 @ 248', '') 
                      WHEN emp.JobDescr LIKE 'ATTEND CK HS PG C3 @ 200 DAYS' THEN REPLACE(emp.JobDescr, 'PG C3 @ 200 DAYS', '') 
                      WHEN emp.JobDescr LIKE 'COUNSELOR ES PG A2 @ 200 DAYS' THEN REPLACE(emp.JobDescr, 'PG A2 @ 200 DAYS', '') 
                      WHEN emp.JobDescr LIKE 'ASSISTANT PRINCIPAL MS PG3@205' THEN REPLACE(emp.JobDescr, 'PG3@205', '') 
                      WHEN emp.JobDescr LIKE 'SWITCHBOARD OP HS PG C2 @ 205' THEN REPLACE(emp.JobDescr, 'PG C2 @ 205', '') 
                      WHEN emp.JobDescr LIKE 'ATTEND CK ES PC C3 @ 200 DAYS' THEN REPLACE(emp.JobDescr, 'C3 @ 200 DAYS', '') 
                      WHEN emp.JobDescr LIKE 'ASST PRINCIPAL HS PG4 @ 210' THEN REPLACE(emp.JobDescr, 'PG4 @ 210', '') 
                      WHEN emp.JobDescr LIKE 'PRINCIPAL MS PG6 @ 220 DAYS' THEN REPLACE(emp.JobDescr, 'PG6 @ 220 DAYS', '') 
                      WHEN emp.JobDescr LIKE 'CN MANAGER HS PG5 @ 178 DAYS' THEN REPLACE(emp.JobDescr, 'PG5 @ 178 DAYS', '') 
                      WHEN emp.JobDescr LIKE 'HEAD CUSTODIAN HS W/HVAC PG A5' THEN REPLACE(emp.JobDescr, 'W/HVAC PG A5', '') 
                      WHEN emp.JobDescr LIKE 'ADM ASST HS PG C6 @ 236 DAYS' THEN REPLACE(emp.JobDescr, 'PG C6 @ 236 DAYS', '') 
                      WHEN emp.JobDescr LIKE 'PRINCIPAL HS PG7 @ 236 DAYS' THEN REPLACE(emp.JobDescr, 'PG7 @ 236 DAYS', '') 
                      WHEN emp.JobDescr LIKE 'PRINCIPAL HS PG6 @ 220 DAYS' THEN REPLACE(emp.JobDescr, 'PG6 @ 220 DAYS', '') ELSE emp.JobDescr END AS 'DemogCode', 
                      munispre.prem_loc AS 'SiteCode', munispre.prem_loc AS 'PrimarySite'
FROM         employee AS emp INNER JOIN
                      [Server\TYLERCI].mu_live.dbo.prempmst AS munispre ON emp.UniqueID = munispre.prem_emp LEFT OUTER JOIN
                      [Server\TYLERCI].mu_live.dbo.praddrss AS munispreaddress ON munispre.prem_emp = munispreaddress.prad_emp AND 
                      munispreaddress.prad_proj = '0' AND munispreaddress.prad_addnum = '1'
WHERE     (munispre.prem_act_stat = 'A') AND (munispre.prem_loc IN ('001', '002', '010', '012', '006', '041', '042', '043', '044', '045', '046', '047', '101', '102', '103', '104', '105', 
                      '106', '107', '108', '109', '110', '111', '112', '113', '114', '115', '116', '117', '118', '119', '120', '121', 'BCTL')) AND (munispre.prem_act_stat = 'A') AND 
                      (emp.JobDescr IN ('TEACHER MS 187 DAYS', 'TEACHER ES 187 DAYS', 'ADM ASST ES PG C5 @ 200 DAYS', 'CN MANAGER MS PG4 @ 178 DAYS', 
                      'COUNSELOR MS HS OR ES PG2', 'TEACHER HS 187 DAYS', 'ADM ASST MS PG C5 @ 205 DAYS',  'ASSISTANT PRIN ES PG3', 
                      'ATTEND CK MS PG C3 @ 200 DAYS',  'ATTEND CK HS PG C3 @ 200 DAYS', 'COUNSELOR ES PG A2 @ 200 DAYS', 
                      'ASSISTANT PRINCIPAL MS PG3@205',  'ATTEND CK ES PC C3 @ 200 DAYS', 'ASST PRINCIPAL HS PG4 @ 210', 
                      'PRINCIPAL MS PG6 @ 220 DAYS', 'CN MANAGER HS PG5 @ 178 DAYS', 'ADV ACADEMIC SPECIALIST HS',  
                      'ADM ASST HS PG C6 @ 236 DAYS', 'PRINCIPAL HS PG7 @ 236 DAYS', 'PRINCIPAL HS PG6 @ 220 DAYS'))

Select allOpen in new window

with t as ( ... your query here...)
select
    id,
    stuff((
        select ',' + t.SiteCode
          from t
         where t.EmployeeID=t.EmployeeID
           and t.Username=t1.Username
           and t.LastName=t1.LastName
           and t.MiddleName=t1.MiddleName
           and t.HireDate=t1.HireDate
           and t.Email=t1.Email
           and t.ActiveAccount=t1.ActiveAccount
           and t.GroupTypeID=t1.GroupTypeID
           and t.DemogCode=t1.DemogCode
         order by t.SiteCode
           for xml path('')
    ),1,1,'') as SiteCodes,
    stuff((
        select ',' + t.GroupTypeID
          from t
         where t.EmployeeID=t.EmployeeID
           and t.Username=t1.Username
           and t.LastName=t1.LastName
           and t.MiddleName=t1.MiddleName
           and t.HireDate=t1.HireDate
           and t.Email=t1.Email
           and t.ActiveAccount=t1.ActiveAccount
           and t.GroupTypeID=t1.GroupTypeID
           and t.DemogCode=t1.DemogCode
         order by t.SiteCode
           for xml path('')
    ),1,1,'') as GroupTypeIDs
from t t1
group by EmployeeID, Username, LastName, MiddleName, HireDate, Email, ActiveAccount, GroupTypeID, DemogCode

Select allOpen in new window