Solved

Horizontal rows convert into vertical rows - auto

Posted on 2013-11-10
9
2,306 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 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 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

623 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