Unpivot with column name
Solution 1
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;
Solution 2
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:
- create multiple copies for each row using cross join (also creating subject column in this case)
- create column "marks" and fill in relevant values using case expression ( ex: if subject is science then pick value from science column)
-
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;
Solution 3
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;
Tilak
profile for Tilak on Stack Exchange, a network of free, community-driven Q&A sites http://stackexchange.com/users/flair/327255.png .NET Software programmer with interest in WPF, Linq, Debugging, Multithreading, and Design Patterns. Profile
Updated on November 01, 2020Comments
-
Tilak over 3 years
I have a table
StudentMarks
with columnsName, Maths, Science, English
. Data is likeName, 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
-
LBogaardt about 7 years@bluefeet Is there a way such that you don't need to specify the names (Maths, Science, English)? I am doing this operation to many tables, all with the same structure but with different column names.
-
jjjjjjjjjjj about 7 years@LBogaardt No, you need to explicitly define the columns to include.
-
Taryn about 7 years@LBogaardt Take a look at my answer here, you could use dynamic sql to unpivot without specifying the column names.
-
Cristian Scutaru over 5 yearsThis also works with any RDBMS! VALUES, when not available, can be replaced by a subquery with SELECT ... UNION ... SELECT ... Wondering about the performance of that CROSS JOIN though...