September 1, 2015 at 9:00 am
I have recently taken over running the internal leagues at the squash club I play at
I was hoping to put these online for members to view and add results as required
The league structure follows the below format with 6 leagues
league 1
| | John | Mark | Peter | Martin | Paul |
|:------:|:----:|:----:|:-----:|:------:|:----:|
| John | NULL | 3 | 0 | 1 | 2 |
| Mark | 0 | NULL | 1 | 3 | 0 |
| Peter | 3 | 3 | NULL | 1 | 3 |
| Martin | 3 | 1 | 3 | NULL | 2 |
| Paul | 3 | 3 | 0 | 3 | NULL |
league 2
etc etc
I have designed the table structure as
CREATE TABLE [dbo].[Results](
[ResultId] [int] IDENTITY(1,1) NOT NULL,
[LeagueId] [int] NOT NULL,
[Player1Id] [int] NOT NULL,
[Player2Id] [int] NOT NULL,
[Player1Result] [int] NULL,
[Player2Result] [int] NULL)
CREATE TABLE [dbo].[Players](
[PlayerId] [int] IDENTITY(1,1) NOT NULL,
[UserId] [int] NOT NULL,
[FirstName] [nvarchar](150) NULL,
[LastName] [nvarchar](150) NULL)
CREATE TABLE [dbo].[Leagues](
[LeagueId] [int] IDENTITY(1,1) NOT NULL,
[LeagueName] [nvarchar](50) NULL)
I am trying to write a query which gives me the output of each divsion in one query rather than several to give me the output
can anyone help with the query?
what i have so far is
select p.FirstName, p1.player2result, p2.player2result, p3.player2result, p4.player2result
from
(select player2Result from Results p1 where p.playerId = p1.Player2Id
union
select player2Result from Results p2 where p.playerId = p2.Player2Id
union
select player2Result from Results p3 where p.playerId = p3.Player2Id
union
select player2Result from Results p4 where p.playerId = p4.Player2Id) as opResult
LEFT JOIN Players p on opResult.Player2Result = p.PlayerId
GROUP BY p.FirstName, p1.player2result, p2.player2result, p3.player2result, p4.player2result
September 1, 2015 at 2:31 pm
September 3, 2015 at 10:41 am
How about the following somewhat dynamic cross tab?
CREATE TABLE #Leagues (
LeagueId int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
LeagueName nvarchar(50) NULL
);
CREATE TABLE #Players (
PlayerId int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
UserId int NOT NULL,
FirstName nvarchar(150) NULL,
LastName nvarchar(150) NULL
);
CREATE TABLE #Results (
ResultId int IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED,
LeagueId int NOT NULL,
Player1Id int NOT NULL,
Player2Id int NOT NULL,
Player1Result int NULL,
Player2Result int NULL
);
INSERT INTO #Leagues (LeagueName) VALUES ('Biblical Names');
INSERT INTO #Players (UserId, FirstName, LastName)
VALUES (1, 'John', 'Davidson'),
(2, 'Mark', 'Johnson'),
(3, 'Peter', 'Paulson'),
(4, 'Martin', 'Stevenson'),
(5, 'Paul', 'Williamson');
INSERT INTO #Results (LeagueId, Player1Id, Player2Id, Player1Result, Player2Result)
VALUES (1, 1, 2, 1, 0),--John beats Mark 3 times
(1, 1, 2, 1, 0),
(1, 1, 2, 1, 0),
(1, 1, 3, 0, 1),--John loses to Peter 3 times
(1, 1, 3, 0, 1),
(1, 1, 3, 0, 1),
(1, 1, 4, 1, 0),--John beats Martin once, and loses 3 times
(1, 1, 4, 0, 1),
(1, 1, 4, 0, 1),
(1, 1, 4, 0, 1),
(1, 1, 5, 1, 0),--John beats Paul twice, and loses 3 times
(1, 1, 5, 1, 0),
(1, 1, 5, 0, 1),
(1, 1, 5, 0, 1),
(1, 1, 5, 0, 1),
(1, 2, 3, 1, 0),--Mark beats Peter once, and loses 3 times
(1, 2, 3, 0, 1),
(1, 2, 3, 0, 1),
(1, 2, 3, 0, 1),
(1, 2, 4, 1, 0),--Mark beats Martin 3 times, and loses once
(1, 2, 4, 1, 0),
(1, 2, 4, 1, 0),
(1, 2, 4, 0, 1),
(1, 2, 5, 0, 1),--Mark loses to Paul 3 times
(1, 2, 5, 0, 1),
(1, 2, 5, 0, 1),
(1, 3, 4, 1, 0),--Peter beats Martin once, and loses 3 times
(1, 3, 4, 0, 1),
(1, 3, 4, 0, 1),
(1, 3, 4, 0, 1),
(1, 3, 5, 1, 0),--Peter beats Paul 3 times
(1, 3, 5, 1, 0),
(1, 3, 5, 1, 0),
(1, 4, 5, 1, 0),--Martin beats Paul twice, and loses 3 times
(1, 4, 5, 1, 0),
(1, 4, 5, 0, 1),
(1, 4, 5, 0, 1),
(1, 4, 5, 0, 1);
DECLARE @LEAGUE_ID AS int = 1;
DECLARE @SQL AS varchar(max);
DECLARE @SQL_SUMS AS varchar(200) = '';
WITH PLAYER_DATA AS (
SELECT DISTINCT R.Player1Id, P.FirstName
FROM #Leagues AS L
INNER JOIN #Results AS R
ON L.LeagueId = R.LeagueId
INNER JOIN #Players AS P
ON R.Player1Id = P.PlayerId
WHERE L.LeagueId = @LEAGUE_ID
UNION
SELECT DISTINCT R.Player2Id, P.FirstName
FROM #Leagues AS L
INNER JOIN #Results AS R
ON L.LeagueId = R.LeagueId
INNER JOIN #Players AS P
ON R.Player2Id = P.PlayerId
WHERE L.LeagueId = @LEAGUE_ID
)
SELECT @SQL_SUMS = @SQL_SUMS + ', SUM([' + CAST(Player1Id AS varchar(5)) + ']) AS [' + FirstName + ']' + CHAR(13) + CHAR(10)
FROM PLAYER_DATA
ORDER BY Player1Id;
--PRINT @SQL_SUMS;
SET @SQL =
'WITH RESULTS AS (
SELECT L.LeagueId, L.LeagueName, P1.PlayerId AS Player1Id, P1.FirstName Player1Name, R.Player2Id, P2.FirstName AS Player2Name,
SUM(R.Player1Result) AS Player1Wins, SUM(R.Player2Result) AS Player1Losses
FROM #Leagues AS L
INNER JOIN #Results AS R
ON L.LeagueId = R.LeagueId
INNER JOIN #Players AS P1
ON R.Player1Id = P1.PlayerId
INNER JOIN #Players AS P2
ON R.Player2Id = P2.PlayerId
WHERE L.LeagueId = ' + CAST(@LEAGUE_ID AS varchar(5)) + '
GROUP BY L.LeagueId, L.LeagueName, P1.PlayerId, P1.FirstName, R.Player2Id, P2.FirstName
),
CROSS_TAB_VALUES AS (
SELECT LeagueName, Player1Name, Player1Id,
SUM([1]) AS [1], SUM([2]) AS [2], SUM([3]) AS [3], SUM([4]) AS [4], SUM([5]) AS [5]
FROM RESULTS
PIVOT (SUM(Player1Wins) FOR Player2Id IN ([1], [2], [3], [4], [5])) AS PVT
GROUP BY LeagueName, Player1Name, Player1Id
UNION ALL
SELECT LeagueName, Player2Name, Player2Id,
SUM([1]) AS [1], SUM([2]) AS [2], SUM([3]) AS [3], SUM([4]) AS [4], SUM([5]) AS [5]
FROM RESULTS
PIVOT (SUM(Player1Losses) FOR Player1Id IN ([1], [2], [3], [4], [5])) AS PVT
GROUP BY LeagueName, Player2Name, Player2Id
)
SELECT C.LeagueName, C.Player1Id, C.Player1Name' + CHAR(13) + CHAR(10) + @SQL_SUMS +
'FROM CROSS_TAB_VALUES AS C
GROUP BY C.LeagueName, C.Player1Name, Player1Id
ORDER BY C.LeagueName, C.Player1Id';
--PRINT @SQL;
EXEC (@SQL);
It encodes your table creates and the presented results as data to work with. Here's the results:
| | John | Mark | Peter | Martin | Paul |
|:------:|:------:|:------:|:------:|:------:|:------:|
| John | NULL | 3 | 0 | 1 | 2 |
| Mark | 0 | NULL | 1 | 3 | 0 |
| Peter | 3 | 3 | NULL | 1 | 3 |
| Martin | 3 | 1 | 3 | NULL | 2 |
| Paul | 3 | 3 | 0 | 3 | NULL |
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply