Horizontal rows convert into vertical rows - auto

Dear Experts,
I am using the follow statement to auto generate Horizontal Rows in vertical.

Declare @SC Varchar(100)
Declare @Cyear Varchar(100)
Set @Sc='02'
Set @Cyear='1314'

--Create Data Temp Table
Drop Table #Result
Create Table #Result (Transno Varchar(200), ExamCode Varchar(100), SC Varchar(100), SN Varchar(100), ObtMarks Numeric(15,2), d int)
Insert Into #Result(Transno, ExamCode, SC, SN, ObtMarks,d)
select a.Transno, ExamCode=a.examcode+'-1', a.Sc, b.Sn, a.Om, d=1
from result_det a 
Join Subject b on a.sc=b.subjectid
Join result_mst c on a.transno=c.transno 
Join Examduration d on a.examcode=d.examcode 
where c.Cyear=@Cyear
Union All
select a.Transno, ExamCode=a.examcode+'-2', a.Sc, b.Sn, a.Om1, d=2
from result_det a 
Join Subject b on a.sc=b.subjectid
Join result_mst c on a.transno=c.transno 
Join Examduration d on a.examcode=d.examcode 
where c.Cyear=@Cyear
Order by ExamCode, transno,a.Sc
Create Unique Index XI_Result On #Result (Transno, examCode, Sc, sn,ObtMarks, d)
Select * from #Result where ObtMarks >0

---Create Column Detail
Drop Table #Column
Create Table #Column (Ident Int Identity(0,1), Sn Varchar(100))
Insert Into #Column(SN)
Select SN from #Result Group by sn,sc 
--(INDEX(XI_TransportCost), NOLOCK)
order by Sc
Create Unique Index XI_Column on #Column (Ident, Sn)
Select * from #Column 

--Create Row Detail

Drop Table #Row
Create Table #Row (Transno Varchar(200), ExamCode varchar(20))
Insert Into #Row(TRansNo,ExamCode)
SELECT TransNo, ExamCode=ExamCode FROM #Result 
--(INDEX(XI_TransportCost), NOLOCK)
GRoup by TransNo,ExamCode,d
order by TransNo,ExamCode,d
Create Unique Index XI_Row on #Row(TransNo, ExamCode)
--Select * From #Row


Declare @ColumnIndex Int
Declare @MaxColumn Int
Declare @ColumnText Varchar(100)
Declare @Sql varchar(8000)


Select 	@ColumnIndex=0,
	@MaxColumn=Max(Ident)
from #Column

---print @MaxColumn
While @ColumnIndex <= @MaxColumn
Begin
	Select @ColumnText=Sn
	From #Column
	where @ColumnIndex = Ident


Select @SQL= 'ALTER TABLE #Row ADD ' + QUOTENAME(@ColumnText) + ' Numeric(15,2) Default 0'
Exec   (@SQL)

	SELECT @SQL = 	'UPDATE #Row SET ' + QUOTENAME(@ColumnText) + ' = #Result.ObtMarks
                      	FROM #Result (INDEX(XI_Result), NOLOCK), #Column 
			WHERE #Row.TransNo = #Result.TransNo and #Row.ExamCode = #Result.ExamCode 
			AND #Column.SN = #Result.SN 
			AND #Column.Ident = ' + CAST(@ColumnIndex AS VARCHAR(12))
        EXEC  (@SQL)
--Print @SQL

--Select * from #Row

Select @ColumnIndex = @ColumnIndex + 1

End

DROP TABLE #Column
DROP TABLE #Result

SELECT     #Row.*
FROM        #Row
ORDER BY   #Row.TransNo, #Row.ExamCode

Select allOpen in new window

#Result table result are as follows:

#Column table result are as follows:

#Row table result are as follows:

The last statement is showing error:

and also showing result but like this (without ObtMarks)

Please help me to fix this error and get final result with ObtMarks.

Rgds.
Mehram