Unpivot with column name

Question

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

score 232 · Accepted Answer · edited Apr 27 '18 at 16:20

232

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

edited Apr 27 '18 at 16:20

Sergey Kalinichenko

714,442
84
1,110
1,523

answered Sep 27 '13 at 16:48

Taryn

242,637
56
362
405

@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. – LBogaardt Feb 08 '17 at 09:23
1

@LBogaardt No, you need to explicitly define the columns to include. – jjjjjjjjjjj Feb 09 '17 at 15:32
1

@LBogaardt Take a look at my answer [here](http://stackoverflow.com/a/18026337/426671), you could use dynamic sql to unpivot without specifying the column names. – Taryn Feb 09 '17 at 15:36

score 11 · Answer 2 · answered Feb 23 '18 at 21:51

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;

This 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... — Cristian Scutaru, Oct 16 '18 at 20:39

score 5 · Answer 3 · edited Sep 12 '22 at 14:52

5

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;

edited Sep 12 '22 at 14:52

Charlieface

52,284
6
19
43

answered Nov 01 '19 at 02:46

Jeyhun

83
1
5

Unpivot with column name

3 Answers3

Linked

Related