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

User · Answer

SELECT   FROM student   UNPIVOT Marks FOR subjects in Maths   Science   English

User · Answer

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

User · Answer

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

User · 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    See SQL Fiddle with demo

[sql] Unpivot with column name

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql

Examples related to unpivot