SQL Server 2000 跨表交叉查询

Question

我有4个表，想要动态生成输出，但我需要将表格grade和sport的行转换为列。下面提供的表格具有示例数据。 student grade enrol sport 这是我期望的输出： 输出显示了在教师91下注册的学生，并显示所有运动项目（作为列），并显示其在2014学年的对应等级。我们使用的是SQL Server 2000（我想我应该感到难过）。我尝试了很多在互联网上找到的查询，但它们都不起作用，而且通常是静态制作的（如果您已经知道要显示哪些列）。

交叉表本身有点麻烦，我正在使用多个表格使其更加复杂。我已经遇到这个问题超过3个月了，仍然没有成功实现我的期望输出。顺便说一句，我正在ASP.Net的SqlDataSource上编写代码，并将其绑定到GridView上。

- eirishainjel

---

唯一的方法是使用动态SQL，如果您事先不知道运动名称。从表中动态生成汇总行和连接行到一个字符串变量中，并执行它。请使用以下答案之一。 - Hogan

我尝试使用动态SQL，但它一直弹出错误，如未声明变量等。它能在SQL Server 2000上工作吗？ - eirishainjel

Answer 1

版本SQL 2000：

DECLARE @dynamicCols VARCHAR(8000);
SET @dynamicCols = '';

SELECT @dynamicCols = @dynamicCols+ 
       ', SUM(CASE WHEN sport.sportid='''+sportid+''' THEN grade.grade END) AS ['+sport+'] '
FROM sport

EXECUTE ( 
      'SELECT student.idnumber AS ID, student.student AS Student '
      +@dynamicCols+
      'FROM student
       JOIN grade on student.idnumber=grade.idnumber
       JOIN enrol on enrol.sportid=grade.sportid
       JOIN sport on sport.sportid=enrol.sportid 
       WHERE enrol.teacher=''91'' AND enrol.sy=''2014''
       GROUP BY student.idnumber, student.student
       ORDER BY student.student')

---

原始答案

好的，既然没有人站出来，我将展示如何动态地完成它。

基于YOusaFZai手动完成的模板，首先创建选择列表中的动态项，然后将它们合并到其余的选择语句中，最后执行这个动态语句：

DECLARE @sqlSTR VARCHAR(MAX);
DECLARE @dynamicCols VARCHAR(MAX);

SELECT @dynamicCols = 
(
  SELECT ', SUM(CASE WHEN sport.sportid='''+sportid+''' THEN grade.grade END) AS ['+sport+'] '
  FROM sport
  FOR XML PATH('')
)

--PRINT @dynamicCols

SELECT @sqlSTR = 
      'SELECT student.idnumber AS ID, student.student AS Student '+@dynamicCols+
      'FROM student
       JOIN grade on student.idnumber=grade.idnumber
       JOIN enrol on enrol.sportid=grade.sportid
       JOIN sport on sport.sportid=enrol.sportid 
       WHERE enrol.teacher=''91'' AND enrol.sy=''2014''
       GROUP BY student.idnumber, student.student
       ORDER BY student.student'

EXEC(@sqlSTR)

高级：

有时您可能会预期到不良数据，您可以保护自己。例如，如果成绩表中可能存在不在运动表中的运动ID，则可以像以下示例更改第一个查询以包含结果中的不良数据：

SELECT @dynamicCols = 
(
  SELECT DISTINCT ', SUM(CASE WHEN grade.sportid='''+grade.sportid+''' THEN grade.grade END) AS ['+ISNULL(sport.sport,'ILLEGAL ID #'+grade.sportid)+'] '
  FROM grade
  LEFT JOIN sport ON sport.sportid = grade.sportid
  FOR XML PATH('')
)

- Hogan

---

您提供的代码在 MSSQL Server 2005 上可以运行（但仍有错误），但当我尝试在我正在使用的 Server 2000 上运行时，它并不好用。提示说有未声明的内容。但是，这对我来说是非常大的帮助。我正在尝试修改它并尝试在 SQL Server 2000 上运行。谢谢您！ - eirishainjel

@eirishainjel -- 一个未声明的什么？ - Hogan

@eirishainjel - 根据您提供的数据，2005年的版本没有错误。也许您没有正确复制它？ - Hogan

似乎SQL Server 2000不接受VARCHAR(MAX)，我已经修复了那些未声明的错误。现在，最后的错误是“关键字'Select'附近的语法不正确”和“'XML'附近的语法不正确”。我只是复制了你给我的代码，它有这些错误。 - eirishainjel

是的，完全正确 @Hogan :) - Shell

Answer 2

在不支持PIVOT运算符的SQL Server 2000中，交叉表透视查询通常使用聚合函数（如MAX）构建。以下是一个视图定义，可生成所需的结构。请注意，您的运动列必须完全枚举所有可能的grade.sportid值（添加了值“spo99”以进行演示）。

CREATE VIEW StudentSportsGrades
AS
SELECT s.idnumber, s.student, sp.teacher
,   MAX(CASE WHEN g.sportid=N'spo1' THEN g.grade END) AS [Soccer]
,   MAX(CASE WHEN g.sportid=N'spo17' THEN g.grade END) AS [Tennis]
,   MAX(CASE WHEN g.sportid=N'spo3' THEN g.grade END) AS [Chess]
,   MAX(CASE WHEN g.sportid=N'spo99' THEN g.grade END) AS [SomeOtherSport]
FROM Student AS s
INNER JOIN grade AS g ON g.idnumber=s.idnumber
INNER JOIN sport AS sp ON sp.sportid=g.sportid
GROUP BY s.idnumber, s.student, sp.teacher
GO

这里有一个查询可以生成你需要的结果。

SELECT idnumber, student, Soccer, Tennis, Chess
FROM StudentSportsGrades
WHERE teacher=91
ORDER BY student

这是一个静态结构，所以它可能与您在搜索中看到的其他示例相同。但将其包装在视图中提供了一些机会，可以使用不同的列和过滤条件生成结果。

另请参阅SQL Server 2000中的交叉表查询，其中链接了使用动态SQL实现此目的的方法。

- Joel Allison

---

我已经知道并尝试了这段代码，但我意识到它不是我所需要的，因为正如我在问题中所述，运动是动态“创建”的。这意味着，根据SY，运动可能会改变，每个学生都参加不同的运动。但还是谢谢。 :) - eirishainjel

Answer 3

声明足球、网球和国际象棋的列为静态。

select s.idnumber,student,
sum(case when sp.sportid='spo1' then grade end) as soccer
,sum(case when sp.sportid='spo17' then grade end) as Tennis
,sum(case when sp.sportid='spo3' then grade end) as Chess
from student s 
inner join grade g on s.idnumber=g.idnumber
inner join enrol e on e.sportid=g.sportid
inner join sport sp on sp.sportid=e.sportid and sp.sy=e.sy 
where sp.teacher='91'
group by s.idnumber,student

- YOusaFZai

---

我已经知道并尝试了这段代码，但我意识到它不是我所需要的，因为正如我在问题中所述，运动是动态“创建”的。这意味着，根据SY，运动可能会改变，每个学生都参加不同的运动。但还是谢谢。 :) - eirishainjel