# 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
``````

#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)

Rgds.
Mehram
Microsoft SQL Server 2008

Last Comment
Mehram

8/22/2022 - Mon
PortletPaul

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Mehram

Sir,
If you see I am using two different columns in union

Om & Om1

Om is carrying test obtain marks
Om1 is carrying result obtain marks

in first row I am displaying text obtain marks of same exam
in the second row displaying exam obtain marks

Please guide how to handle this?

Mehram
PortletPaul

``````SELECT
a.Transno
, ExamCode = a.examcode + cj.suffix
, a.Sc
, b.Sn
, case when cj.d =1 then a.Om else a.Om1 end
, cj.d
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
CROSS JOIN ( SELECT '-1' AS suffix, 1 as d UNION ALL
SELECT '-2' AS suffix, 2 as d
) AS cj
WHERE c.Cyear = @Cyear
;
``````
Mehram

Sir,
Only last thing, I need to display Transno in order with examcode

like
Trans #    ExamCode
xxxxxx1   FT-01 -1
xxxxxx1   FT-01 -2
Mehram

Excellent
Mehram

Sir,