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, 2022 in Database by Kithuzzz
• 38,010 points
561 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, 2022 by narikkadan
• 63,700 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, 2022 in Database by Kithuzzz
• 38,010 points
387 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, 2022 in Database by Kithuzzz
• 38,010 points
499 views
0 votes
0 answers

How to update Identity Column in SQL Server?

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

Aug 9, 2022 in Database by Kithuzzz
• 38,010 points
1,140 views
0 votes
0 answers

Simple DateTime sql query

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

Aug 12, 2022 in Database by Kithuzzz
• 38,010 points
465 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, 2022 in Database by Kithuzzz
• 38,010 points
429 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, 2022 in Database by Kithuzzz
• 38,010 points
296 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, 2022 in Database by narikkadan
• 63,700 points
2,137 views
0 votes
1 answer

SQL Server WITH statement

I'm going to guess because I don't ...READ MORE

answered Sep 10, 2022 in Database by narikkadan
• 63,700 points
476 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP