select query in which a column values come as column headers

Question

stdId   StdName Subname  SubjectMark
---------------------------------------------
1   alex    english    50
2   anto    english    60
2   anto    hindhi     60
2   anto    science    30
2   anto    math       20
3   abru    math       70
3   abru    hindhi     60
3   abru    english    50

i have a table as shown above. i want to write a query to get values as shown below

student     english  hindhi   science   math
----------------------------------------------
alex           50   
anto           60       60      30   
abru           50       70               70

please help me.. thanks in advance

It is quite easy, but you have to show what have you tried. There is no question in your "question". You expect someone to do all the work for you. Nobody will. — Jakub Matczak, Sep 10 '13 at 12:12
Which database are you using? Some have built-in support for pivot queries. — Joe White, Sep 10 '13 at 12:12
try to look at this. [http://stackoverflow.com/questions/10925445/mysql-select-dynamic-row-values-as-column-names-another-column-as-value][1] this may help you. [1]: http://stackoverflow.com/questions/10925445/mysql-select-dynamic-row-values-as-column-names-another-column-as-value — Moshe Zino, Sep 10 '13 at 12:16

score 0 · Accepted Answer · answered Sep 10 '13 at 14:06

You did not specify what database you are using but you should be able to use an aggregate function with a CASE expression in any database to convert the rows of data into columns. This process is known as PIVOT:

select stdname,
  max(case when subname = 'english' then subjectmark end) english,
  max(case when subname = 'hindi' then subjectmark end) hindi,
  max(case when subname = 'science' then subjectmark end) science,
  max(case when subname = 'math' then subjectmark end) math
from yourtable 
group by stdname;

See SQL Fiddle with Demo

select query in which a column values come as column headers

1 Answers1