--create table HospitalInfo(
--Years       int,
--Code        nvarchar(10),
--Name        nvarchar(100),
--Prop1       bit,
--Prop2       bit,
--Prop3       bit,
--Prop4       bit)

--insert into HospitalInfo values
--(1990,'900001','Hospital1',0,0,0,0),
--(1991,'900001','Hospital1',0,0,0,0),
--(1992,'900001','Hospital1',1,0,0,0),
--(1993,'900001','Hospital1',0,1,0,0),
--(1994,'900001','Hospital1',0,0,1,0),
--(1995,'900001','Hospital1',0,0,0,1),
--(1990,'900002','Hospital2',0,0,0,0),
--(1991,'900002','Hospital2',0,0,0,1),
--(1992,'900002','Hospital2',0,0,1,0),
--(1993,'900002','Hospital2',0,1,0,0),
--(1994,'900002','Hospital2',1,0,0,0),
--(1995,'900002','Hospital2',0,0,0,0),
--(1990,'900003','Hospital3',0,0,0,0),
--(1991,'900003','Hospital3',1,1,0,0),
--(1992,'900003','Hospital3',0,0,1,1),
--(1993,'900003','Hospital3',0,1,0,0),
--(1994,'900003','Hospital3',1,0,0,0),
--(1995,'900003','Hospital3',0,0,0,0)

select * from HospitalInfo

--先计算有多少家医院
select ROW_NUMBER() over (order by code) as id,Code into #Hospital from HospitalInfo group by Code

declare @MinID int
select @MinID=MIN(id) from #Hospital
print @MinID
while @MinID is not null
begin
    declare @Code nvarchar(10)
    select @Code=Code from #Hospital where id=@MinID--当前医院

    declare @MinColumnID int
    select @MinColumnID=MIN(column_id) from sys.columns where object_id=object_id('HospitalInfo') and name not in ('Years','Code','Name')--要计算的列（属性）
    while @MinColumnID is not null
    begin
        declare @ColumnName nvarchar(100)
        select @ColumnName=name from sys.columns where object_id=object_id('HospitalInfo') and name not in ('Years','Code','Name') and column_id=@MinColumnID--当前列
        --拿到最小为1的年份
        declare @Sql nvarchar(1000)
        declare @Paramery nvarchar(1000)
        declare @MinYear int
        set @Sql='select @Yearout=MIN(Years) from HospitalInfo where Code='+@Code+' and '+@ColumnName+'=1'
        set @Paramery='@Yearout int output'
        exec sp_executesql @Sql,@Paramery,@Yearout=@MinYear output

        --更新
        set @Sql='update HospitalInfo set '+@ColumnName+'=1 where Code='+@Code+' and Years>='+Convert(varchar(4),@MinYear)
        print @Sql
        exec(@Sql)

        select @MinColumnID=MIN(column_id) from sys.columns where object_id=object_id('HospitalInfo') and name not in ('Years','Code','Name') and column_id>@MinColumnID--下个计算列
    end
    select @MinID=MIN(id) from #Hospital where id>@MinID--下家医院
end
drop table #Hospital
go