I created a table with your sample data
create table StudentMark (REG_NO int,REF_NO int,SUB varchar(3),MARKS int)
insert into StudentMark values
(16015090,015047, 'GSC', 9),
(16015090,015047, 'SSC', 28),
(16015090,015047, 'MTH', 21),
(16015090,015047, 'TLS', 4),
(16015090,015047, 'FLO', 44),
(16015090,015047, 'SLE', 2),
(16033545,033114, 'MTH', 13),
(16033545,033114, 'TLH', 39),
(16033545,033114, 'FLO', 12),
(16033545,033114, 'SLE', 13),
(16033545,033114, 'GSC', 5),
(16033545,033114, 'SSC', 11),
(16056438,056007, 'FLO', 19),
(16056438,056007, 'SLE', 10),
(16056438,056007, 'MTH', 25),
(16056438,056007, 'TLS', 21),
(16056438,056007, 'GSC', 23),
(16056438,056007, 'SSC', 17)
select * from StudentMark
Solution Query
select ROW_NUMBER()over(partition by REG_NO order by SUB)Slno,* into #prps from StudentMark
select distinct REG_NO,REF_NO into #detail from StudentMark
DECLARE @slno int
select @slno =max(slno) from #prps
DECLARE @i varchar(10),@alter varchar(4000),@value varchar(4000)
SET @i=1
while(@i<=@slno)
begin
select @alter='alter table #detail add SUB'+@i+' varchar(100),Marks'+@i+' varchar(100)'
select @value='update #detail set SUB'+@i+'=b.sub,Marks'+@i+'=b.Marks from #detail a join #prps b on a.REG_NO=b.REG_NO where slno='+@i
exec (@alter)
exec (@value)
set @i=@i+1
end
select * from #detail
Output
REG_NO REF_NO SUB1 Marks1 SUB2 Marks2 SUB3 Marks3 SUB4 Marks4 SUB5 Marks5 SUB6 Marks6
16015090 15047 FLO 44 GSC 9 MTH 21 SLE 2 SSC 28 TLS 4
16033545 33114 FLO 12 GSC 5 MTH 13 SLE 13 SSC 11 TLH 39
16056438 56007 FLO 19 GSC 23 MTH 25 SLE 10 SSC 17 TLS 21