Unpivot with column name

Question

I have a table StudentMarks with columns Name, Maths, Science, English. Data is like

Name,  Maths, Science, English  

Tilak, 90,    40,      60  

Raj,   30,    20,      10

I want to get it arranged like the following:

Name,  Subject,  Marks

Tilak, Maths,    90

Tilak, Science,  40

Tilak, English,  60

With unpivot, I am able to get Name, Marks properly, but not able to get the column name in the source table to the Subject column in the desired result set.

How can I achieve this?

I have so far reached the following query (to get Name, Marks)

select Name, Marks from studentmarks

Unpivot( Marks for details in (Maths, Science, English)

) as UnPvt

Answer 1

You can use the query as follows which includes the subject in the final select list:

select u.name, u.subject, u.marks

from student s

unpivot

(

  marks

  for subject in (Maths, Science, English)

) u;

For more information, you can refer to SQL Fiddle with demo