如何使用动态查询SQL连接三个表并将行转换为列 [英] How to join three tables and convert rows into columns using dynamic query SQL

问题描述

I have three tables dbo.Student col(StudentID,Student Name and

Subjects                Test
---------------         ----------------
subjectId int           StubjectId int foreignkey reference Subjects
Subject Name            StudentID int  foreignkey reference  Student
                        Marks int
Now I need to display the test Result As

Student Name     Math   Geo 
----------------------------
Michael          90     67

我的尝试：

What I have tried:

SELECT *
FROM 
(
	SELECT 
	[FRMSIXWKTST.SubjectId],Marks
	FROM FRMSIXWKTST,Subjects		
)as s
PIVOT
(
	AVG(Marks)
	FOR [Subject Name] IN (SubjectId)
)as Pivot_alias

推荐答案

这是类似的东西，你可以学习并将它与你的任务联系起来。

SQL PIVOT行到JOIN表的列 [ ^ ]

Here is something similar that you can study and relate it to your assignment.
SQL PIVOT Rows to Columns with JOIN Tables[^]

BEGIN --CREATE TEMP TABLE
	DECLARE @Sport TABLE (
		SportId		INT,
		SportName		VARCHAR(50)
	)

	DECLARE @Game TABLE (
        SportId		INT,
		AthleteId		INT,
		Scores			INT
	)

	DECLARE @Athlete TABLE (
		AthleteId		INT,
		AthleteName		VARCHAR(50)
	)
END

BEGIN --INSER INTO @Athlete TABLE
	INSERT INTO @Athlete
		SELECT 1, 'Michael'
		UNION
		SELECT 2, 'Richard';
END

BEGIN --INSER INTO @Sport TABLE
	INSERT INTO @Sport
		SELECT 1, 'Ping Pong'
		UNION
		SELECT 2, 'Basketball'
		UNION
		SELECT 3, 'Football';
END

BEGIN --INSER INTO @Game TABLE
	INSERT INTO @Game
		SELECT 1, 1, 90
		UNION
		SELECT 2, 1, 67
		UNION
		SELECT 3, 1, 78
		UNION
		SELECT 1, 2, 77
		UNION
		SELECT 2, 2, 87
		UNION
		SELECT 3, 2, 54;
END

SELECT AthleteName, [Ping Pong], Basketball
FROM 
(
	SELECT 
	 AthleteName, SportName, Scores  
	 FROM @Game t JOIN  @Athlete s
			ON t.AthleteId = s.AthleteId
		JOIN @Sport sub 
			ON t.SportId = sub.SportId
	WHERE t.AthleteId = 1
)as s
PIVOT
(
	AVG(Scores)
	FOR SportName IN ([Ping Pong], Basketball, Football)
)as Pivot_alias

输出：

Output:

AthleteName  Ping Pong Basketball
Michael      90        67

这篇关于如何使用动态查询SQL连接三个表并将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！