How to pivot this data ?

Question

Post reply

How to pivot this data ?

SQL Kidu

Ten Centuries

Points: 1296
More actions
July 23, 2010 at 6:55 am

#225370

Hi All,
I have a score table where different parameters and scores are kept.
Please see the sample below
Year Month Student IDSubjectIDMarks
20092 1 1 30
20092 1 2 45
20092 2 1 36
20092 3 1 50
20092 3 2 40
Subject ID Subject Name
1 Maths
2 Science
3 English
Student ID Student Name
1 Priyan
2 Zid
3 Kareena
I want the result to be shown like
Priyan Zid Kareena
Maths 30 36 50
Science 45 0 40
English 0 0 0
How can we obtain this ? Can anyone help ?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Will there be only 3 students or u have got more students ??

SQL Kidu Ten Centuries Points: 1296 More actions · Answer 2

Yes. It will be more. the number is not fixed. means the number of columns varies

LutzM SSC Guru Points: 107049 More actions · Answer 3

It seems like DynamicCrossTab is the best solution for this task.

Please have a look at the related article referenced in my signature.

If you'd like to see a coded version based on your sample data please provide data in a ready to use format as described in the first link in my sig.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

sanujss (7/23/2010)
I want the result to be shown like
Priyan Zid Kareena
Maths 30 36 50
Science 45 0 40
English 0 0 0
How can we obtain this ? Can anyone help ?

Just an idea....but I would have expected that you will probably have more students than subjects...would it be better to present the results this way?

MathsScienceEnglish

Priyan30450

Zid3600

Kareena50400

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

gah (7/24/2010)
Just an idea....but I would have expected that you will probably have more students than subjects...would it be better to present the results this way?
MathsScienceEnglish
Priyan30450
Zid3600
Kareena50400

Exactly my point as well.. if there are more thatn 100 students, imagine how ugly your presentation will be with > 100 columns.. Rather u have only select subjects.. it will easier and simple if u pivot by name on subjects rather than otherwise..