Unpivot with column name

Question

I have a StudentMarks table with columns Name, Maths, Science, English . The data is similar to

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

I want to configure it as follows:

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

With univot, I can correctly identify Name, Marks, but could not get the column name in the source table in Subject in the desired result set.

How can i achieve this?

I still got to the next request (to get Name, Marks)

 select Name, Marks from studentmarks Unpivot ( Marks for details in (Maths, Science, English) ) as UnPvt 

Answer 1

Your request is very close. You should be able to use the following, which includes subject in the final selection list:

 select u.name, u.subject, u.marks from student s unpivot ( marks for subject in (Maths, Science, English) ) u; 

See SQL Fiddle with a Demo

Answer 2

You can also try the standard sql deployment method using a logic sequence with the following code. The following code consists of 3 steps:

1. create multiple copies for each row using a cross join (in this case also create a topic column)
2. create a column “marks” and fill in the corresponding values ​​using an expression of the case (for example: if the subject is science, then select the value from the science column)

3. remove any null combinations (if exists, the table expression can be completely avoided if there are no null values ​​in the 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; 

Answer 3

Thank. Great answer.

select u.name, u.subject, u.marks from the student’s scan (grades on the subject in (mathematics, natural sciences, English)) u;

Answer 4

CHOOSE * FROM student

UNPIVOT (grades in subjects (mathematics, science, English));