How to make this query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to make this query

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-06-06 : 22:05:39

hi,
I have three tables with the following fields:

User:
UserID
Name

Test:
TestID
Name

Grade:
GradeID
TestID
UserID
Score

And I want to display the result something like this:

UserID Name Test1 Test2 Test3
1 Azam 23 34 45
2 Ali 34 45 56

How can this be accomplised thanks,

Mohammad Azam
www.azamsharp.net

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2005-06-06 : 22:37:24

Hi,
Where did you get these fields Test1 Test2 Test3 from? since the fields which you mentioned in three tables don't have these fields.Thanks

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-06-06 : 22:40:18

Thanks for the reply. Test1, Test2, Test3 are the score fields. Since testID can be different meaning different scores.

TestID = 1 maybe the score of the Test 1
TestID = 2 maybe the score of the Test 2

and so on.

I hope I cleared the question.

Mohammad Azam
www.azamsharp.net

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-06 : 23:03:20

What you are looking for is called a Crosstab.

There is an article here, http://www.sqlteam.com/item.asp?ItemID=2955
and a bunch of great blog posts here http://weblogs.sqlteam.com/jeffs/

they should help you along.

Damian
Ita erat quando hic adveni.

azamsharp
Posting Yak Master

201 Posts

Posted - 2005-06-06 : 23:05:24

Thanks I was kinda thinking of SELF JOIN.

I guess I was wrong !

Mohammad Azam
www.azamsharp.net

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources