Unpivot with column name

0 votes

Name, Maths, Science, and English are the columns in my table StudentMarks. Data looks like:

Name,  Maths, Science, English  
Tilak, 90,    40,      60  
Raj,   30,    20,      10

I'd want to see it set up as follows:

Name,  Subject,  Marks
Tilak, Maths,    90
Tilak, Science,  40
Tilak, English,  60

I can correctly obtain Name and Marks using Unpivot, but I am unable to link the name of the Subject column in the source table to the necessary result set.

How do I accomplish this?

Sep 14 in Database by Kithuzzz
• 11,640 points
13 views

1 answer to this question.

0 votes

Your inquiry is quite near. The following should work for you and 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.

answered Sep 16 by narikkadan
• 19,680 points

Related Questions In Database

0 votes
1 answer
0 votes
1 answer
0 votes
0 answers

Find all tables containing column with specified name - MS SQL Server

Is it feasible to run a search ...READ MORE

Aug 29 in Database by Kithuzzz
• 11,640 points
27 views
0 votes
0 answers

How to concat two columns into one with the existing column name in mysql?

Using MySQL, I want to combine two ...READ MORE

Sep 6 in Database by Kithuzzz
• 11,640 points
13 views
0 votes
0 answers

How to update Identity Column in SQL Server?

With 200 records currently, I want to ...READ MORE

Aug 9 in Database by Kithuzzz
• 11,640 points
159 views
0 votes
0 answers

Simple DateTime sql query

How do I query the DateTime database ...READ MORE

Aug 12 in Database by Kithuzzz
• 11,640 points
22 views
0 votes
0 answers

How can I select the first day of a month in SQL?

I only need to choose the given ...READ MORE

Aug 14 in Database by Kithuzzz
• 11,640 points
25 views
0 votes
0 answers

CROSS JOIN vs INNER JOIN in SQL

What is the difference between CROSS JOIN and INNER JOIN? CROSS ...READ MORE

Aug 18 in Database by Kithuzzz
• 11,640 points
19 views
0 votes
1 answer

Add a column with a default value to an existing table in SQL Server

Syntax: ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT ...READ MORE

answered Sep 12 in Database by narikkadan
• 19,680 points
52 views
0 votes
1 answer

Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452) in sql 2008

Solution Go to Start > Programs > Microsoft SQL Server > Enterprise Manager. Right-click the SQL ...READ MORE

answered Sep 12 in Database by narikkadan
• 19,680 points
33 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP