x

Dynamic pivoting on hash table

I have the data in followings format

aa rr Title AssignmentType Title AssignmentType

A1 1 asgn3 only A 10900231 asgn3 only A 5 15 A1 1 asgn3 only A 10900531 asgn3 only A 6 15 A1 1 asgn3 only A 10900657 asgn3 only A 7 15 A1 1 asgn3 only A 10901021 asgn3 only A 7 15

A2 1 asgn3 only A 10900231 asgn3 only A 7 15 A2 1 asgn3 only A 10900531 asgn3 only A 9 15 A2 1 asgn3 only A 10900657 asgn3 only A 10 15 A2 1 asgn3 only A 10901021 asgn3 only A 11 15

FROM THIS query

Select a.AssignmentType+CONVERT(varchar,rr) aa , * from ( Select Row_number() over (partition by Assignmenttype Order By Assignmenttype) as rr, * from ( Select Distinct TeacherAssignment.Title,TeacherAssignment.AssignmentType from TeacherAssignment where TeacherAssignment.TeacherLoginName='11611' and TeacherAssignment.CourseCode='CAP314' and TeacherAssignment.Section='TB903' and TeacherAssignment.MaxMarks is NOT NULL

) tmp ) a , ( Select TeacherAssignment.RegisterationNumber, TeacherAssignment.Title, TeacherAssignment.AssignmentType,TeacherAssignment.MarksObtained,TeacherAssignment.MaxMarks from TeacherAssignment where TeacherAssignment.TeacherLoginName='11611' and TeacherAssignment.CourseCode='CAP314' and TeacherAssignment.Section='TB903' and TeacherAssignment.MaxMarks is NOT NULL --Order By Assignmenttype,TeacherAssignment.Title ) b Where a.AssignmentType+a.Title = b.AssignmentType+b.Title

union

Select a.AssignmentType aa, * from ( Select Row_number() over (partition by AssignmentType Order By AssignmentType) as rr, * from ( Select distinct PracticalComponent.PracticalCode As Title,PracticalComponent.PracticalCode As AssignmentType from PracticalComponent,PracticalCompnentDetail where PracticalComponent.Id = PracticalCompnentDetail.Id and PracticalComponent.FacultyId='11611' and PracticalComponent.Coursecode='CAP314' and PracticalCompnentDetail.Section='TB903' ) a

 ) a ,

 (
 Select PracticalCompnentDetail.RegistrationNumber ,PracticalComponent.PracticalCode As Title,PracticalComponent.PracticalCode As AssignmentType,
 PracticalCompnentDetail.CompMMObt As MarksObtained,PracticalComponent.CompTMM As MaxMarks   
 from PracticalComponent,PracticalCompnentDetail where PracticalComponent.Id = PracticalCompnentDetail.Id and                 
 PracticalComponent.FacultyId='11611' and PracticalComponent.Coursecode='CAP314' and PracticalCompnentDetail.Section='TB903'
     --Order By Assignmenttype,TeacherAssignment.Title

) b WHERE a.AssignmentType=b.AssignmentType --Order by a.AssignmentType+CONVERT(varchar,rr),a.AssignmentType

my boss told me that data comes from above query save into #table and then do dyanmic pivoting on hash table and data comes in following format

RegNo A1 A2 A3

10900231 5 7 ---

10900531 6 9 ----

10900657 7 10 --

Plz help me i will Really Apperciate if some one help me

more ▼

asked Apr 15, 2010 at 10:34 AM in Default

avatar image

Manpreet thakur
1 3 3 3

is this not the same question as before?

Apr 15, 2010 at 10:41 AM Kev Riley ♦♦

Merged the other question into this one to preserve Rob's answer, and the (slightly) more specific question text

Apr 15, 2010 at 07:21 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2030
x114
x58

asked: Apr 15, 2010 at 10:34 AM

Seen: 1528 times

Last Updated: Apr 15, 2010 at 04:51 PM

Copyright 2017 Redgate Software. Privacy Policy