---12

您希望创建一个动态透视表。

CREATE TABLE #T1 (StudentID int,SubjectID INT, Subject varchar(10), Marks int)
INSERT INTO #T1 VALUES (1, 10, 'Chem',82)
INSERT INTO #T1 VALUES (1,11, 'Phy',89)
INSERT INTO #T1 VALUES (1,12, 'Maths',88)
INSERT INTO #T1 VALUES (1,13, 'Eng',80)
INSERT INTO #T1 VALUES (2, 10, 'Chem',80)
INSERT INTO #T1 VALUES (2,11, 'Phy',85)
INSERT INTO #T1 VALUES (2,12, 'Maths',91)
INSERT INTO #T1 VALUES (2,13, 'Mgmt',82)
DECLARE @PivotColumnHeaders varchar(MAX)
SELECT @PivotColumnHeaders =
  COALESCE(
    @PivotColumnHeaders + ',[' + UC.Subject + ']',
    '[' + Subject + ']'
  )
FROM (SELECT Subject FROM #T1 GROUP BY Subject) UC

DECLARE @PQuery varchar(MAX) = '
SELECT * FROM (SELECT StudentID, Subject, Marks FROM #T1 T0) T1
PIVOT (SUM([Marks]) FOR Subject IN (' + @PivotColumnHeaders + ') ) AS P'
EXECUTE (@PQuery)

DROP TABLE #T1

结果是：

StudentID   Chem    Eng   Maths  Mgmt   Phy
   1         82      80     88   NULL    89
   2         80     NULL    91    82     85

----数学-88
1

----化学-82
1

--- 82-- 89 --- 88 ---- 80
注：这些“-”仅用于描述表格。抱歉，格式不好。

---11

---13

---10

----英语----80
目标表如下所示：
学生证物理化学数学工程
1

您可以使用 case 表达式：

select student_id, 
       sum(case when Subject _ID = 10 then marks else 0 end) as Chem,
       sum(case when Subject _ID = 11 then marks else 0 end) as Phy,
       sum(case when Subject _ID = 12 then marks else 0 end) as Maths,
       sum(case when Subject _ID = 13 then marks else 0 end) as Eng
from table t
group by student_id;

----89物理
1