SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table v_DelProdProps (id int, prop_val float, prop_str_val nvarchar(20), prop_name varchar(20));
insert into v_DelProdProps values 
(152,    7, null,         'TPSA'),
(152,   12, null,         'ROT'),

(153,    9, null,         'TPSA'),
(153, null, null,         'ROT'),
(153, null, 'LNNXCA-LNB', 'BBBarcode')
5 rows affected

with tmp as (
  select id, prop_name, 
         case when prop_val is null then prop_str_val 
              else cast(prop_val as nvarchar(50)) 
         end prop_val
  from v_DelProdProps )
select * from tmp
pivot (max(prop_val) 
       for prop_name in ( [TPSA], [ROT], [BBBarcode]) ) as pvt


id
TPSA
ROT
BBBarcode


152
7
12
null

153
9
null
LNNXCA-LNB


with tmp as (
  select id, prop_name, prop_val, prop_str_val,
         prop_name as prop_name_bar
  from v_DelProdProps )
select id, max(tpsa) tpsa, max(rot) rot, max(bbbarcode) bbarcode from tmp
pivot (max(prop_val) for prop_name in ( [TPSA], [ROT]) ) as pvt1
pivot (max(prop_str_val) for prop_name_bar in ( [BBBarcode]) ) as pvt2
group by id


id
tpsa
rot
bbarcode


152
7
12
null

153
9
null
LNNXCA-LNB


Warning: Null value is eliminated by an aggregate or other SET operation.


select id, 
        max(case when prop_name = 'TPSA' then prop_val end) tpsa,
        max(case when prop_name = 'ROT' then prop_val end) rot,
        max(case when prop_name = 'BBBarcode' then prop_str_val end) tpsa
from v_DelProdProps 
group by id


id
tpsa
rot
tpsa


152
7
12
null

153
9
null
LNNXCA-LNB


Warning: Null value is eliminated by an aggregate or other SET operation.