SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table First
(
	Id int not null
	,IdCust int not null
	,Ref int not null
)
insert First
(ID      ,IdCust   ,Ref)
values
 (1       ,300      ,123)
,(1       ,300      ,124)
,(2       ,302      ,345)
3 rows affected

create table Second 
(
	Id int not null
	,Ref int not null
	,Code char(1) not null
	,Price int not null
)
insert Second
(ID     ,Ref      ,Code    ,Price)
values
 (1      ,123      ,'A'       ,10)
,(1      ,123      ,'Y'       ,15)
,(2      ,124      ,'A'       ,14)
,(3      ,345      ,'C'       ,18);
4 rows affected

declare @sql nvarchar(max) = 'select id, IdCust, Ref';
select @sql = @sql + '
		,max(case when Seq = 1 then stock end) as [Stock' + rowNumVarchar + '] 
		,max(case when Seq = 1 then code end) as [Code' + rowNumVarchar + ']
		,max(case when Seq = 1 then price end) as [Price' + rowNumVarchar + ']
'
from 
(
	select distinct cast(row_number() over (partition by ref order by ref) as nvarchar) rowNumVarchar
	from second s
) z;
set @sql = @sql + '
	from (select f.*, s.Id Stock, s.Code, s.Price,
					row_number() over (partition by f.Ref order by s.id) as Seq
			from first f
			inner join second s on s.Ref = f.Ref 
			) t
	group by id, IdCust, Ref;
';
exec (@sql)


id
IdCust
Ref
Stock1
Code1
Price1
Stock2
Code2
Price2


1
300
123
1
A
10
1
A
10

1
300
124
2
A
14
2
A
14

2
302
345
3
C
18
3
C
18


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