Result display! [modified]

[Learner520]

hi everyone, I have 2 tables like StudentDetail and TuitionLevel StudentDetail id Name Surname StudentUniqueId Subject 1 sam smith G1234U890 E 2 sam smith G1234U890 M 3 Paul Day R4321B009 E E as English M as Maths TuitionLevel id PreLevel PostLevel 1 3 4 2 5 4 3 2 2 I want to display result like this Name MathsPre MathsPost EngPre EngPost sam 3 4 5 4 Paul 2 2 I have done in the following way but this is not what i need. SELECT [Name],Surname,StudentUniqueId, Case when motot.Subject = 'E' Then 'English' Else 'Maths' end AS Subject, Pre, IsNull(Post,'') as Post FROM StudentDetail sd INNER JOIN TuitionLevel TL on TL.id =sd.id ORDER BY StudentUniqueId any help would be much appreciated. thanks in adv.

modified on Wednesday, April 7, 2010 11:16 AM

[Richard Blythe]

Learner520 wrote:

INNER JOIN TuitionLevel TL on TL.id =sd.id

What does the tuitionID have to do with the studentID? Also, what database are you communicating with?

If my answer has helped you, one of my articles may also be a help. Also remember that your best friend's name is google.

[Mycroft Holmes]

This article may be of use to you, assuming you are using SQL 2005 or later Pivot two or more columns in SQL Server 2005[^] And yes that join is suspicious. Actually your data structure is screwed, not that you have sma smith stored twice, this is wrong. Suggested restructure:

Student
StudentID
StudentName

Subject
SubjectID
Subject

TuitionLevels
LevelID
Description

LinkTable
StudentID
SubjectID
LevelID

Never underestimate the power of human stupidity RAH

[Learner520]

Hi Mycroft Holmes, Thanks for your reply. It was my mistake I didnot normaliz data but I did on purpose but it did not work. Now I have normalized it and I'm trying to figure out how to display it through pivot. But still need your help. Any suggestion or hint how to do that would be much appreciated. thanks TuitionLevel tuitionlevelid tuitionleveldesc 1 1a 2 1b 3 1c 4 1 Student UPN StudentName A1234 Alan A1233 Sam A1232 Carol Subject Subjectid SubjectDesc 1 E 2 M UPN subjid pretui_id posttui_id A1234 1 1 2 A1234 2 3 4 A1233 1 7 3 A1233 2 1 4 A1232 1 5 6 A1232 2 6 6 output: UPN StudentName PreMaths postMaths preEng PostEng A1234 Alan 1a 1b 1c 1 Any help would be much appreciated. thanks

[Mycroft Holmes]

If you work through the article I basically do it in 2 steps. Build up the query that will service the pivot. Get your student details, the course details and your performance information into a normal result set, do not get any extra data like ID information that is not needed. Get a distinct list of the course names (either from the result set or all the courses), these will be your pivoted columns. I would do a hard coded mini query to get a feel for it, once you get a result by hard coding the pivot columns you can turn it into dynamic sql. To debug the dynamic sql print the text and try and run that.

Never underestimate the power of human stupidity RAH