0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professionals and students of engineering

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, 'Backetball'
		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], Backetball
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], Backetball, Football)
)as Pivot_alias

fork query

edited nov 21 anonymous

created sep 27 17 bryian.tan