select
StaffIDNo, SurName,FirstName, FatherName,emailID ,SPOUSE ,LocalAddress,Designation,MobileSELF ,Mobilespouse,BloodGroup,CL,
DD,MM ,YY , Teacher , NonTeacherfrom ( --AAAAselect * from (select Row_number() OVER( order by F1) as rno1 ,F2 as 'StaffIDNo' from EMPLOYEEDATA$ where F1 = 'STAFF ID NO.') ajoin (select Row_number() OVER( order by F1) as rno2 ,F2 as 'SurName' from EMPLOYEEDATA$ where F1 = 'sur name') b on a.rno1 = b.rno2 join (select Row_number() OVER( order by F4) as rno3 ,F5 as 'FirstName' from EMPLOYEEDATA$ where F4 = 'First') c on b.rno2 = c.rno3 join (select Row_number() OVER( order by F1) as rno4 ,F2 as 'FatherName' from EMPLOYEEDATA$ where F1 = 'Fathers Name') d on c.rno3 = d.rno4 join (select Row_number() OVER( order by F4) as rno5 ,F5 as 'emailID' from EMPLOYEEDATA$ where F4 = 'e-mail ID') e on d.rno4 = e.rno5 join (select Row_number() OVER( order by F4) as rno6 ,F5 as 'SPOUSE' from EMPLOYEEDATA$ where F4 = 'SPOUSE') f on e.rno5 = f.rno6 join (select Row_number() OVER( order by F1) as rno7 ,F2 as 'LocalAddress' from EMPLOYEEDATA$ where F1 = 'Local Address') g on f.rno6 = g.rno7 join (select Row_number() OVER( order by F1) as rno8 ,F2 as 'Designation' from EMPLOYEEDATA$ where F1 = 'Designation') h on g.rno7 = h.rno8 join (select Row_number() OVER( order by F1) as rno9 ,F2 as 'MobileSELF' from EMPLOYEEDATA$ where F1 = 'Mobile (SELF)') i on h.rno8 = i.rno9 join (select Row_number() OVER( order by F1) as rno10 ,F2 as 'Mobilespouse' from EMPLOYEEDATA$ where F1 = 'Mobile (spouse)') j on i.rno9 = j.rno10 join (select Row_number() OVER( order by F3) as rno11 ,F4 as 'BloodGroup' from EMPLOYEEDATA$ where F3 = 'Blood Group') k on j.rno10 = k.rno11 join (select Row_number() OVER( order by F5) as rno12 ,F6 as 'CL' from EMPLOYEEDATA$ where F5 = 'CL-') l on k.rno11 = l.rno12 join (select Row_number() OVER( order by F4) as rno13 ,F5 as 'DD' from EMPLOYEEDATA$ where F4 = 'DD:') m on l.rno12 = m.rno13 join (select Row_number() OVER( order by F6) as rno14 ,F7 as 'MM' from EMPLOYEEDATA$ where F6 = 'MM:') n on m.rno13 = n.rno14 join (select Row_number() OVER( order by F8) as rno15 ,F9 as 'YY' from EMPLOYEEDATA$ where F8 = 'YY:') o on n.rno14 = o.rno15 join (select Row_number() OVER( order by F6) as rno16 ,case when F7 = '?' then 'Y' else F7 end as 'Teacher' from EMPLOYEEDATA$ where F6 = 'Teacher') p on o.rno15 = p.rno16 join (select Row_number() OVER( order by F8) as rno17 ,case when F9 = '?' then 'Y' else F9 end as 'NonTeacher' from EMPLOYEEDATA$ where F8 = 'Non-Teacher') q on p.rno16 = q.rno17 ) A -- order by firstname , surname -- AAAA
select
StaffIDNo, SurName,FirstName, FatherName,emailID ,SPOUSE ,LocalAddress,Designation,MobileSELF ,Mobilespouse,BloodGroup,CL,DD,MM ,YY , Teacher , NonTeacherfrom ( --AAAAselect * from (select Row_number() OVER( order by F1) as rno1 ,F2 as 'StaffIDNo' from EMPLOYEEDATA$ where F1 = 'STAFF ID NO.') ajoin (select Row_number() OVER( order by F1) as rno2 ,F2 as 'SurName' from EMPLOYEEDATA$ where F1 = 'sur name') b on a.rno1 = b.rno2 join (select Row_number() OVER( order by F4) as rno3 ,F5 as 'FirstName' from EMPLOYEEDATA$ where F4 = 'First') c on b.rno2 = c.rno3 join (select Row_number() OVER( order by F1) as rno4 ,F2 as 'FatherName' from EMPLOYEEDATA$ where F1 = 'Fathers Name') d on c.rno3 = d.rno4 join (select Row_number() OVER( order by F4) as rno5 ,F5 as 'emailID' from EMPLOYEEDATA$ where F4 = 'e-mail ID') e on d.rno4 = e.rno5 join (select Row_number() OVER( order by F4) as rno6 ,F5 as 'SPOUSE' from EMPLOYEEDATA$ where F4 = 'SPOUSE') f on e.rno5 = f.rno6 join (select Row_number() OVER( order by F1) as rno7 ,F2 as 'LocalAddress' from EMPLOYEEDATA$ where F1 = 'Local Address') g on f.rno6 = g.rno7 join (select Row_number() OVER( order by F1) as rno8 ,F2 as 'Designation' from EMPLOYEEDATA$ where F1 = 'Designation') h on g.rno7 = h.rno8 join (select Row_number() OVER( order by F1) as rno9 ,F2 as 'MobileSELF' from EMPLOYEEDATA$ where F1 = 'Mobile (SELF)') i on h.rno8 = i.rno9 join (select Row_number() OVER( order by F1) as rno10 ,F2 as 'Mobilespouse' from EMPLOYEEDATA$ where F1 = 'Mobile (spouse)') j on i.rno9 = j.rno10 join (select Row_number() OVER( order by F3) as rno11 ,F4 as 'BloodGroup' from EMPLOYEEDATA$ where F3 = 'Blood Group') k on j.rno10 = k.rno11 join (select Row_number() OVER( order by F5) as rno12 ,F6 as 'CL' from EMPLOYEEDATA$ where F5 = 'CL-') l on k.rno11 = l.rno12 join (select Row_number() OVER( order by F4) as rno13 ,F5 as 'DD' from EMPLOYEEDATA$ where F4 = 'DD:') m on l.rno12 = m.rno13 join (select Row_number() OVER( order by F6) as rno14 ,F7 as 'MM' from EMPLOYEEDATA$ where F6 = 'MM:') n on m.rno13 = n.rno14 join (select Row_number() OVER( order by F8) as rno15 ,F9 as 'YY' from EMPLOYEEDATA$ where F8 = 'YY:') o on n.rno14 = o.rno15 join (select Row_number() OVER( order by F6) as rno16 ,case when F7 = '?' then 'Y' else F7 end as 'Teacher' from EMPLOYEEDATA$ where F6 = 'Teacher') p on o.rno15 = p.rno16 join (select Row_number() OVER( order by F8) as rno17 ,case when F9 = '?' then 'Y' else F9 end as 'NonTeacher' from EMPLOYEEDATA$ where F8 = 'Non-Teacher') q on p.rno16 = q.rno17 )
A -- order by firstname , surname -- AAAA
No comments:
Post a Comment