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
Advertisement
Answer
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;