Solved

Horizontal rows convert into vertical rows - auto

Posted on 2013-11-10
9
2,262 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

751 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