使用列名逆透视

2021-12-01 00:00:00 sql tsql sql-server-2008 sql-server unpivot

我有一个表 StudentMarks，其中包含 Name、Maths、Science、English 列.数据就像

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

我想按如下方式安排:

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

使用 unpivot 我是能够正确获取Name、Marks，但无法将源表中的列名获取到所需结果集中的Subject列.

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

推荐答案

您的查询非常接近.您应该能够使用以下内容，其中包括最终选择列表中的 subject:

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;

参见SQL Fiddle with demo

相关文章