declare @GEODETAIL table (Holeid varchar(20), geolfrom float, geolto float, name varchar(20),value varchar(20))
insert into @GEODETAIL (holeid, geolfrom, geolto, name, value) values
('hr-1',0,8,'Lith_RockName','Песчанник'),
('hr-1',8,50,'Lith_RockName','Алевролит'),
('hr-1',50,100,'Lith_RockName','Известняк'),
('hr-1',100,108,'Lith_RockName','Гравелиты'),
('hr-1',108,200,'Lith_RockName','Алевролиты'),
('hr-1',0,5,'Sec_RockName','Глина'),
('hr-1',30,38,'Alt_RockName','Переслаивание'),
('hr-1',120,180,'Min_RockName','Минерализация'),
('hr-1',190,200,'Min_RockName','Минерализация')
select a.*,others.* from @GEODETAIL a 
outer apply (Select * From (Select geolfrom as geolfrom_sec, geolto as geolto_sec, value as 'Sec_RockName',
                                   row_number() over (order by geolfrom) as rn_sec 
                              From @GEODETAIL b 
                             Where a.holeid = b.holeid and b.name = 'Sec_RockName' 
                               and a.geolto>b.geolfrom and a.geolfrom<=b.geolto) Sec
					  full join 
					        (Select geolfrom as geolfrom_alt, geolto as geolto_alt, value as 'Alt_RockName',
                                   row_number() over (order by geolfrom) as rn_alt
                              From @GEODETAIL b 
                             Where a.holeid = b.holeid and b.name = 'Alt_RockName' 
                               and a.geolto>b.geolfrom and a.geolfrom<=b.geolto) alt
						 on rn_sec = rn_alt
					  full join 
					        (Select geolfrom as geolfrom_min, geolto as geolto_min, value as 'Min_RockName',
                                   row_number() over (order by geolfrom) as rn_min
                              From @GEODETAIL b 
                             Where a.holeid = b.holeid and b.name = 'Min_RockName' 
                               and a.geolto>b.geolfrom and a.geolfrom<=b.geolto) min
						 on isnull(rn_sec,rn_alt) = rn_min
) others
WHere a.name = 'Lith_RockName'

[SUP]
select case row_number() over(partition by a.holeid order by a.geolfrom) when 1 then a.holeid end as x_holeid, 
case row_number() over(partition by a.holeid,a.geolfrom order by a.geolfrom) when 1 then a.GEOLFROM end as geol_from,
case row_number() over(partition by a.holeid,a.geolfrom,a.geolto order by a.geolfrom) when 1 then a.GEOLTO end as geol_to,
case row_number() over(partition by a.holeid,a.geolfrom,a.geolto,a.value order by a.geolfrom) when 1 then a.VALUE end as Lith_RockNameee,



geolfrom_sec,geolto_sec,Lith3_RockName,geolfrom_alt,geolto_alt,Alteration,geolfrom_min,geolto_min,Mineralisation from GEODETAILS a 
outer apply (Select * From (Select geolfrom as geolfrom_sec, geolto as geolto_sec, value as 'Lith3_RockName',
                                   row_number() over (order by geolfrom) as rn_sec 
                              From GEODETAILS b 
                             Where a.holeid = b.holeid and b.name = 'Lith3_RockName' 
                               and a.geolto>b.geolfrom and a.geolfrom<b.geolto) Sec
					  full join 
					        (Select geolfrom as geolfrom_alt, geolto as geolto_alt, value as 'Alteration',
                                   row_number() over (order by geolfrom) as rn_alt
                              From GEODETAILS b 
                             Where a.holeid = b.holeid and b.name = 'Alteration' 
                               and a.geolto>b.geolfrom and a.geolfrom<b.geolto) alt
						 on rn_sec = rn_alt
					  full join 
					        (Select geolfrom as geolfrom_min, geolto as geolto_min, value as 'Mineralisation',
                                   row_number() over (order by geolfrom) as rn_min
                              From GEODETAILS b 
                             Where a.holeid = b.holeid and b.name = 'Mineralisation' 
                               and a.geolto>b.geolfrom and a.geolfrom<b.geolto) min on isnull(rn_sec,rn_alt) = rn_min
) others
WHere a.name = 'Lith_RockName'[/SUB]