sql - 使用 SQL Server 将列转换为多行

Question

这是我的表结构:

CREATE TABLE StudesntMarkList
(
    StudentID int, 
    StudentName varchar(100), 
    Performance varchar(100), 
    class varchar(100),     
    Section varchar(100),   
    subject1 varchar(100), 
    subjectmark1 varchar(100),  
    subject2 varchar(100),  
    subjectmark2 varchar(100),  
    subject3 varchar(100),  
    subjectmark3 varchar(100), 
    subject4 varchar(100),  
    subjectmark4 varchar(100),  
    subject5 varchar(100),  
    subjectmark5 varchar(100),  
    subject6 varchar(100),  
    subjectmark6 varchar(100)
)

插入一些值:

INSERT INTO StudesntMarkList 
VALUES (1, 'shiva', 'not bad', '10th', 'C', 'science', 58, 'social', '', 'english', 70, 'maths', '', 'biology', 67, '', 58)

我有一个看起来像这样的表:

Student ID|Student Name|Performance|class|Section|subject1    |subject mark1|subject2|subject mark2|subject3|subject mark3|subject4|subject mark4|subject5|subject mark5|subject6|subject mark6|
1         |shiva       |not bad    |10th |c      |science     |58           |Social  |             |English |70           |maths   |             |biology |67           |        |50           |

我对使用什么 SQL 查询来获取以下结果集感到困惑:

id|att          |att val|Val
--+-------------+-------+----
1 |Student Name |Shiva  |   
1 |Performance  |not bad|   
1 |class        |10th   |
1 |Section      |c      |
1 |subject1     |science|58
1 |subject2     |Social |   
1 |subject3     |English|70
1 |subject4     |maths  |   
1 |subject5     |biology|67
1 |subject6     |       |50

这可能吗？

请帮我解决这个问题，在此先感谢

Answer 1

我建议你重新设计你的表模式和存储策略，我认为它可能需要做规范化而不是创建很多列来存储数据。

如果您想从原始表中获得预期结果，我们可以尝试使用CROSS APPLY...VALUE

SELECT StudentID,
       v.*
FROM StudesntMarkList 
CROSS APPLY (VALUES 
('StudentName',StudentName,''),
('Performance',Performance,''),
('class',class,''),
('subject1',subject1,subjectmark1),
('subject2',subject2,subjectmark2),
('subject3',subject3,subjectmark3),
('subject4',subject4,subjectmark4),
('subject5',subject5,subjectmark5),
('subject6',subject6,subjectmark6)

) v(att,attval,Val)

sqlfiddle