Unpivot with column name

Question

Name, Maths, Science, and English are the columns in my table StudentMarks. Data looks like:

Name,  Maths, Science, English  
Tilak, 90,    40,      60  
Raj,   30,    20,      10

I'd want to see it set up as follows:

Name,  Subject,  Marks
Tilak, Maths,    90
Tilak, Science,  40
Tilak, English,  60

I can correctly obtain Name and Marks using Unpivot, but I am unable to link the name of the Subject column in the source table to the necessary result set.

How do I accomplish this?

Answer 1

Your inquiry is quite near. The following should work for you and 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;

See SQL Fiddle with demo.