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

Open in new window


#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
MehramAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
PortletPaulConnect With a Mentor Commented:
Not quite sure what else you intend to do with all those temp tables, and I don't understand why you are using a union query to build #result but, the output style indicated can be achieved using PIVOT like this:
|              TRANSNO | EXAMCODE | ENGLISH ( WRITTEN ) | MATHS (WRITTEN) | URDU ( WRITTEN ) |
|----------------------|----------|---------------------|-----------------|------------------|
| 1314FT-01101KG-ICM29 |  FT-01-1 |                  13 |              20 |               19 |
| 1314FT-01101KG-ICM31 |  FT-01-1 |                  12 |              20 |               16 |
| 1314FT-01101KG-ICM32 |  FT-01-1 |                  14 |          (null) |               18 |

Open in new window

This was produced by the following query and data:
DECLARE @cols NVARCHAR(2000)
DECLARE @query NVARCHAR(4000)


/* replace this with real query */
--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, a.examcode, a.Sc, a.Sn, ObtMarks, d
from result_det a


SELECT  @cols = STUFF(
                          (
                          SELECT DISTINCT ',' + quotename(SN)
                          FROM  #Result
                          -- ORDER BY ',' + quotename(SN) /* optionally, sort this */
                          FOR XML PATH('')
                          )
                      , 1, 1, '')

--SELECT  @cols

SET @query = N'SELECT Transno, ExamCode,'
             + @cols
             + ' FROM (SELECT Transno, ExamCode,SN , ObtMarks FROM #Result) AS p PIVOT (MAX([ObtMarks]) FOR SN IN ( '
             + @cols
             + ' )) AS pvt;'

--SELECT @query
EXECUTE(@query)


Drop Table #Result
;

########## data
    CREATE TABLE result_det
    	([Transno] varchar(20), [ExamCode] varchar(20), [SC] varchar(20), [SN] varchar(19), [ObtMarks] Numeric(15,2), [d] int)
    ;
    	
    INSERT INTO result_det
    	([Transno], [ExamCode], [SC], [SN], [ObtMarks], [d])
    VALUES
    	('1314FT-01101KG-ICM31', 'FT-01-1', '06', 'Urdu ( Written )', 16.00, 1),
    	('1314FT-01101KG-ICM31', 'FT-01-1', '09', 'English ( Written )', 12.00, 1),
    	('1314FT-01101KG-ICM31', 'FT-01-1', '13', 'Maths (Written)', 20.00, 1),
    	('1314FT-01101KG-ICM29', 'FT-01-1', '06', 'Urdu ( Written )', 19.00, 1),
    	('1314FT-01101KG-ICM29', 'FT-01-1', '09', 'English ( Written )', 13.00, 1),
    	('1314FT-01101KG-ICM29', 'FT-01-1', '13', 'Maths (Written)', 20.00, 1),
    	('1314FT-01101KG-ICM32', 'FT-01-1', '06', 'Urdu ( Written )', 18.00, 1),
    	('1314FT-01101KG-ICM32', 'FT-01-1', '09', 'English ( Written )', 14.00, 1)
    ;


  [1]: http://sqlfiddle.com/#!3/26396/5

Open in new window

{+edit}
By the way, you might be able to completely avoid the union query when building #result by using a cross join that has 2 rows. e.g. This will output 2 rows for every single row in result_det:
SELECT
        rd.transno
      , rd.examcode + cj.suffix AS examcode
      , cj.d
FROM result_det AS rd
CROSS JOIN ( SELECT '-1' AS suffix, 1 as d UNION ALL 
             SELECT '-2' AS suffix, 2 as d 
           ) AS cj
;

Open in new window

0
 
MehramAuthor Commented:
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
0
 
PortletPaulCommented:
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
;

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

like
Trans #    ExamCode
xxxxxx1   FT-01 -1
xxxxxx1   FT-01 -2
0
 
MehramAuthor Commented:
Excellent
0
 
MehramAuthor Commented:
Sir,
If you are here, please help me to short column the result column should be in serial (SC). Please help
0
 
MehramAuthor Commented:
Order by SC, Transno,ExamCode
0
 
PortletPaulCommented:
is this a problem?

You have to include SC in the select clause and the group by clause
0
 
MehramAuthor Commented:
0
All Courses

From novice to tech pro — start learning today.