Unpivot with column name

Question

Your query is very close. You should be able to use the following 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;

See SQL Fiddle with demo

You may also try standard sql un-pivoting method by using a sequence of logic with the following code.. The following code has 3 steps:

1. create multiple copies for each row using cross join (also creating subject column in this case)
2. create column "marks" and fill in relevant values using case expression ( ex: if subject is science then pick value from science column)

3. remove any null combinations ( if exists, table expression can be fully avoided if there are strictly no null values in base table)

    select *
    from 
    (
       select name, subject,
       case subject
       when 'Maths' then maths
       when 'Science' then science
       when 'English' then english
       end as Marks
   from studentmarks
   Cross Join (values('Maths'),('Science'),('English')) AS Subjct(Subject)
   )as D
   where marks is not null;
   

Another way around using cross join would be to specify column names inside cross join

select name, Subject, Marks 
from studentmarks
Cross Join (
values (Maths,'Maths'),(Science,'Science'),(English,'English')
) un(Marks, Subject)
where marks is not null;