Reputation: 30728
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
Upvotes: 154
Views: 343085
Reputation: 113
Another way around using cross join would be to specify column names inside cross join
select name, Subject, Marks
from studentmarks
Cross Apply (
values (Maths,'Maths'),(Science,'Science'),(English,'English')
) un(Marks, Subject)
where marks is not null;
Upvotes: 6
Reputation: 247880
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;
Upvotes: 238
Reputation: 111
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:
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;
Upvotes: 11