Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Horizontal rows convert into vertical rows - auto

Posted on 2013-11-10
9
Medium Priority
?
2,340 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 49

Accepted Solution

by:
PortletPaul earned 2000 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 49

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 49

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

688 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