troubleshooting Question

Horizontal rows convert into vertical rows - auto

Avatar of Mehram
MehramFlag for Pakistan asked on
Microsoft SQL Server 2008
9 Comments1 Solution2482 ViewsLast Modified:
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

#Result table result are as follows:
Result
#Column table result are as follows:
Column
#Row table result are as follows:
Rows
The last statement is showing error:
Error
and also showing result but like this (without ObtMarks)
eee
Please help me to fix this error and get final result with ObtMarks.

Rgds.
Mehram
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros