select 身份证号码,
MAX(t.单位编号) 单位编号,
MAX(t.姓名) 姓名,
MAX(case when t.rows=1 then convert(varchar(max),疾病名称) end) 疾病1,
MAX(case when t.rows=2 then convert(varchar(max),疾病名称) end) 疾病2,
MAX(t.单位名称) 单位名称,
MAX(t.医院编码) 医院编码,
MAX(t.医院名称) 医院名称,
MAX(t.人员类别) 人员类别,
max(t.性别) 性别
from(select
ROW_NUMBER() over(partition by 身份证号码 order by 姓名) as rows,
case
when len(身份证号码) = 15 and cast(substring(身份证号码,15,1) as int) % 2 = 0 then '女'
when len(身份证号码) = 15 and cast(substring(身份证号码,15,1) as int) % 2 = 1 then '男'
when len(身份证号码) = 18 and cast(substring(身份证号码,17,1) as int) % 2 = 0 then '女'
when len(身份证号码) = 18 and cast(substring(身份证号码,17,1) as int) % 2 = 1 then '男'
else null end as 性别,*

from
dbo.tablename
)t
group by 身份证号码

row_number将group by分组中的人员编号 然后使用case when 进行区分两种疾病的人。