question

Manpreet thakur avatar image
Manpreet thakur asked

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

sql-server-2005pivotdynamic
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
is this not the same question as before?
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
Merged the other question into this one to preserve Rob's answer, and the (slightly) more specific question text
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.