Solved

Horizontal rows convert into vertical rows - auto

Posted on 2013-11-10
9
2,202 Views
Last Modified: 2013-11-11
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
0
Comment
Question by:Mehram
  • 6
  • 3
9 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39637633
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
 

Author Comment

by:Mehram
ID: 39637820
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39637862
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Mehram
ID: 39637880
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
 

Author Closing Comment

by:Mehram
ID: 39637884
Excellent
0
 

Author Comment

by:Mehram
ID: 39638012
Sir,
If you are here, please help me to short column the result column should be in serial (SC). Please help
0
 

Author Comment

by:Mehram
ID: 39638013
Order by SC, Transno,ExamCode
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39638209
is this a problem?

You have to include SC in the select clause and the group by clause
0
 

Author Comment

by:Mehram
ID: 39638345
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question