Home Unpivot with column name
Reply: 2

Unpivot with column name

Tilak Published in 2013-09-27 16:37:11Z

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
  Marks for details in (Maths, Science, English)

) as UnPvt
gunr2171 Reply to 2015-06-05 17:07:11Z

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
  for subject in (Maths, Science, English)
) u;

See SQL Fiddle with demo

Rahul Kohli
Rahul Kohli Reply to 2018-02-23 21:51:15Z

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:

  1. create multiple copies for each row using cross join (also creating subject column in this case)
  2. create column "marks" and fill in relevant values using case expression ( ex: if subject is science then pick value from science column)
  3. remove any null combinations ( if exists, table expression can be fully avoided if there are strictly no null values in base table)

     select *
        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;
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.447852 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO