select distinct eo.NameOfInstitution, s.LastSurname as StudentLastName, s.FirstName as StudentFirstName
, dg.CodeValue as GradeLevel
, seoa.HispanicLatinoEthnicity
, case when srAI.StudentUSI is null then 0 else 1 end as [Fed-AmericanIndian-AlaskaNative]
, case when srAS.StudentUSI is null then 0 else 1 end as [Fed-Asian]
, case when srBl.StudentUSI is null then 0 else 1 end as [Fed-Black-AfricanAmerican]
, case when srNh.StudentUSI is null then 0 else 1 end as [Fed-NativeHawaiian-PacificIslander]
, case when srWh.StudentUSI is null then 0 else 1 end as [Fed-White]
, case when sroAi.StudentUSI is null then '0' else dAi.CodeValue end as [MSDS-AmericanIndian-AlaskaNative]
, case when sroAs.StudentUSI is null then '0' else dAs.CodeValue end as [MSDS-Asian]
, case when sroBl.StudentUSI is null then '0' else dBl.CodeValue end as [MSDS-Black-AfricanAmerican]
, case when sroHi.StudentUSI is null then '0' else dHi.CodeValue end as [MSDS-HispanicOrLatino]
, case when sroNh.StudentUSI is null then '0' else dNh.CodeValue end as [MSDS-NativeHawaiian-PacificIslander]
, case when sroWh.StudentUSI is null then '0' else dWh.CodeValue end as [MSDS-White]
, case when srCh.StudentUSI is null then 0 else 1 end as [Fed-ChooseNotToRespond]
, case when srNs.StudentUSI is null then 0 else 1 end as [Fed-NotSelected]
, case when srOt.StudentUSI is null then 0 else 1 end as [Fed-Other]
, case when sroCh.StudentUSI is null then '0' else dCh.CodeValue end as [MSDS-ChooseNotToRespond]
, case when sroNs.StudentUSI is null then '0' else dNs.CodeValue end as [MSDS-NotSelected]
, case when sroOt.StudentUSI is null then '0' else dOt.CodeValue end as [MSDS-Other]
from edfi.StudentSchoolAssociation ssa
left join edfi.EducationOrganization eo on eo.EducationOrganizationId=ssa.SchoolId
left join edfi.Student s on s.StudentUSI=ssa.StudentUSI
left join edfi.Descriptor dg on dg.DescriptorId=ssa.EntryGradeLevelDescriptorId
left join edfi.StudentEducationOrganizationAssociation seoa on seoa.StudentUSI=ssa.StudentUSI
left join edfi.StudentEducationOrganizationAssociationRace srAI on srAI.StudentUSI=ssa.StudentUSI 
       and srAI.RaceDescriptorId=(select rt.DescriptorId from edfi.Descriptor rt where rt.Namespace='uri://ed-fi.org/RaceDescriptor' and rt.CodeValue='American Indian - Alaska Native')
left join edfi.StudentEducationOrganizationAssociationRace srAS on srAS.StudentUSI=ssa.StudentUSI 
       and srAS.RaceDescriptorId=(select rt.DescriptorId from edfi.Descriptor rt where rt.Namespace='uri://ed-fi.org/RaceDescriptor' and rt.CodeValue='Asian')
left join edfi.StudentEducationOrganizationAssociationRace srBl on srBl.StudentUSI=ssa.StudentUSI 
       and srBl.RaceDescriptorId=(select rt.DescriptorId from edfi.Descriptor rt where rt.Namespace='uri://ed-fi.org/RaceDescriptor' and rt.CodeValue='Black - African American')
left join edfi.StudentEducationOrganizationAssociationRace srNh on srNh.StudentUSI=ssa.StudentUSI 
       and srNh.RaceDescriptorId=(select rt.DescriptorId from edfi.Descriptor rt where rt.Namespace='uri://ed-fi.org/RaceDescriptor' and rt.CodeValue='Native Hawaiian - Pacific Islander')
left join edfi.StudentEducationOrganizationAssociationRace srWh on srWh.StudentUSI=ssa.StudentUSI 
       and srWh.RaceDescriptorId=(select rt.DescriptorId from edfi.Descriptor rt where rt.Namespace='uri://ed-fi.org/RaceDescriptor' and rt.CodeValue='White')
left join mi.StudentEducationOrganizationAssociationMSDSRaceOrder sroAi on sroAi.StudentUSI=ssa.StudentUSI
       and sroAi.RaceDescriptorId=(select rt.DescriptorId from edfi.Descriptor rt where rt.Namespace='uri://ed-fi.org/RaceDescriptor' and rt.CodeValue='American Indian - Alaska Native')
left join edfi.Descriptor dAi on dAi.DescriptorId=sroAi.RaceOrderChoiceDescriptorId
left join mi.StudentEducationOrganizationAssociationMSDSRaceOrder sroAs on sroAs.StudentUSI=ssa.StudentUSI
       and sroAs.RaceDescriptorId=(select rt.DescriptorId from edfi.Descriptor rt where rt.Namespace='uri://ed-fi.org/RaceDescriptor' and rt.CodeValue='Asian')
left join edfi.Descriptor dAs on dAs.DescriptorId=sroAs.RaceOrderChoiceDescriptorId
left join mi.StudentEducationOrganizationAssociationMSDSRaceOrder sroBl on sroBl.StudentUSI=ssa.StudentUSI
       and sroBl.RaceDescriptorId=(select rt.DescriptorId from edfi.Descriptor rt where rt.Namespace='uri://ed-fi.org/RaceDescriptor' and rt.CodeValue='Black - African American')
left join edfi.Descriptor dBl on DBl.DescriptorId=sroBl.RaceOrderChoiceDescriptorId
left join mi.StudentEducationOrganizationAssociationMSDSRaceOrder sroHi on sroHi.StudentUSI=ssa.StudentUSI
       and sroHi.RaceDescriptorId=(select rt.DescriptorId from edfi.Descriptor rt where rt.Namespace='uri://midatahub.org/RaceDescriptor' and rt.CodeValue='Hispanic or Latino')
left join edfi.Descriptor dHi on dHi.DescriptorId=sroHi.RaceOrderChoiceDescriptorId
left join mi.StudentEducationOrganizationAssociationMSDSRaceOrder sroNh on sroNh.StudentUSI=ssa.StudentUSI
       and sroNh.RaceDescriptorId=(select rt.DescriptorId from edfi.Descriptor rt where rt.Namespace='uri://ed-fi.org/RaceDescriptor' and rt.CodeValue='Native Hawaiian - Pacific Islander')
left join edfi.Descriptor dNh on DNh.DescriptorId=sroNh.RaceOrderChoiceDescriptorId
left join mi.StudentEducationOrganizationAssociationMSDSRaceOrder sroWh on sroWh.StudentUSI=ssa.StudentUSI
       and sroWh.RaceDescriptorId=(select rt.DescriptorId from edfi.Descriptor rt where rt.Namespace='uri://ed-fi.org/RaceDescriptor' and rt.CodeValue='White')
left join edfi.Descriptor dWh on DWh.DescriptorId=sroWh.RaceOrderChoiceDescriptorId
left join edfi.StudentEducationOrganizationAssociationRace srCh on srCh.StudentUSI=ssa.StudentUSI 
       and srCh.RaceDescriptorId=(select rt.DescriptorId from edfi.Descriptor rt where rt.Namespace='uri://ed-fi.org/RaceDescriptor' and rt.CodeValue='Choose Not to Respond')
left join edfi.StudentEducationOrganizationAssociationRace srNs on srNs.StudentUSI=ssa.StudentUSI 
       and srNs.RaceDescriptorId=(select rt.DescriptorId from edfi.Descriptor rt where rt.Namespace='uri://midatahub.org/RaceDescriptor' and rt.CodeValue='Not Selected')
left join edfi.StudentEducationOrganizationAssociationRace srOt on srOt.StudentUSI=ssa.StudentUSI 
       and srOt.RaceDescriptorId=(select rt.DescriptorId from edfi.Descriptor rt where rt.Namespace='uri://ed-fi.org/RaceDescriptor' and rt.CodeValue='Other')
left join mi.StudentEducationOrganizationAssociationMSDSRaceOrder sroCh on sroCh.StudentUSI=ssa.StudentUSI
       and sroCh.RaceDescriptorId=(select rt.DescriptorId from edfi.Descriptor rt where rt.Namespace='uri://ed-fi.org/RaceDescriptor' and rt.CodeValue='Choose Not to Respond')
left join edfi.Descriptor dCh on dCh.DescriptorId=sroCh.RaceOrderChoiceDescriptorId
left join mi.StudentEducationOrganizationAssociationMSDSRaceOrder sroNs on sroNs.StudentUSI=ssa.StudentUSI
       and sroNs.RaceDescriptorId=(select rt.DescriptorId from edfi.Descriptor rt where rt.Namespace='uri://midatahub.org/RaceDescriptor' and rt.CodeValue='Not Selected')
left join edfi.Descriptor dNs on dNs.DescriptorId=sroNs.RaceOrderChoiceDescriptorId
left join mi.StudentEducationOrganizationAssociationMSDSRaceOrder sroOt on sroOt.StudentUSI=ssa.StudentUSI
       and sroOt.RaceDescriptorId=(select rt.DescriptorId from edfi.Descriptor rt where rt.Namespace='uri://ed-fi.org/RaceDescriptor' and rt.CodeValue='Other')
left join edfi.Descriptor dOt on dOt.DescriptorId=sroOt.RaceOrderChoiceDescriptorId
order by eo.NameOfInstitution,s.LastSurname,s.FirstName