create table tb(ID varchar(10),TYPE varchar(10),length decimal(10,2))
insert into tb select '5309622','DT',30.00
insert into tb select '8002619','BR',15.00
insert into tb select '1002619','CO',30.00
insert into tb select '1002619','BR',30.00
go
;with c1 as(
select row_number()over(order by (select 1))rn,id,'CA100' as col from (select distinct id from tb)t
),c2 as(
select a.rn,b.type,convert(varchar(10),b.length)col from c1 a inner join tb b on a.id=b.id
)
select * from c1 union all select * from c2 order by rn
/*
-------------------- ---------- ----------
1                    1002619    CA100
1                    CO         30.00
1                    BR         30.00
2                    5309622    CA100
2                    DT         30.00
3                    BR         15.00
3                    8002619    CA100

(7 行受影响)

*/
go
drop table tb