Internal Sports League SQL query

Transact-SQL
1

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
2

is the league and division the same thing?

3

I don't understand what info the Division 1 layout is supposed to convey. What do the numbers mean?

Why is there a LeagueId when you have a LeagueNo?

4

is the league and division the same thing.. it is yes i have updated the post to remove the word division

5

sorry that was a mistake, league number shouldnt have been there. Have updated the post

6

Maybe this will get you some of the way:

CREATE TABLE #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 #Players(
	[PlayerId] [int] IDENTITY(1,1) NOT NULL,
	[UserId] [int] NOT NULL,
	[FirstName] [nvarchar](150) NULL,
	[LastName] [nvarchar](150) NULL)
;

CREATE TABLE #Leagues(
	[LeagueId] [int] IDENTITY(1,1) NOT NULL,
	[LeagueName] [nvarchar](50) NULL)
;

insert into #leagues(leaguename)
 values ('Semifinal 1')
       ,('Semifinal 2')
;

insert into #players(userid,firstname,lastname)
 values (1,'John','Doe')
       ,(2,'Mark','Doe')
       ,(3,'Peter','Doe')
       ,(4,'Martin','Doe')
       ,(5,'Paul','Doe')
       ,(6,'Lucas','Doe')
       ,(7,'Ethan','Doe')
       ,(8,'Jacob','Doe')
       ,(9,'Jack','Doe')
;

insert into #results(leagueid,player1id,player2id,player1result,player2result)
 values (1,1,2,3,0)
       ,(1,1,3,0,3)
       ,(1,1,4,1,3)
       ,(1,1,5,2,3)
       ,(1,2,1,0,3)
       ,(1,2,3,1,3)
       ,(1,2,4,3,1)
       ,(1,2,5,0,3)
       ,(1,3,1,3,0)
       ,(1,3,2,3,1)
       ,(1,3,4,1,3)
       ,(1,3,5,3,0)
       ,(1,4,1,3,1)
       ,(1,4,2,1,3)
       ,(1,4,3,3,1)
       ,(1,4,5,2,3)
       ,(1,5,1,3,2)
       ,(1,5,2,3,0)
       ,(1,5,3,0,3)
       ,(1,5,4,3,2)
       
       ,(2,6,7,3,0)
       ,(2,6,8,0,3)
       ,(2,6,9,1,3)
       ,(2,7,6,0,3)
       ,(2,7,8,1,3)
       ,(2,7,9,3,1)
       ,(2,8,6,3,0)
       ,(2,8,7,3,1)
       ,(2,8,9,1,3)
       ,(2,9,6,3,1)
       ,(2,9,7,1,3)
       ,(2,9,8,3,1)
;

with cte_players(leagueid,playerid)
  as (select leagueid
            ,playerid
        from (select l.leagueid
                    ,r.player1id as playerid
                from #leagues as l
                     inner join #results as r
                             on r.leagueid=l.leagueid
               group by l.leagueid
                       ,r.player1id
              union all
              select l.leagueid
                    ,r.player2id as playerid
                from #leagues as l
                     inner join #results as r
                             on r.leagueid=l.leagueid
               group by l.leagueid
                       ,r.player2id
             ) as p
       group by leagueid
               ,playerid
     )
select leaguename
      ,firstname
      ,p1,p2,p3,p4,p5
  from (select cp1.leagueid
              ,l.leaguename
              ,cp1.playerid
              ,p.firstname
              ,r.player1result as result
              ,'p'+ltrim(str(row_number() over(partition by cp1.leagueid,cp1.playerid order by cp2.playerid))) as rn
          from cte_players as cp1
               inner join #leagues as l
                       on l.leagueid=cp1.leagueid
               inner join #players as p
                       on p.playerid=cp1.playerid
               left outer join cte_players as cp2
                            on cp2.leagueid=cp1.leagueid
               left outer join #results as r
                            on r.leagueid=cp1.leagueid
                           and r.player1id=cp1.playerid
                           and r.player2id=cp2.playerid
       ) as r
       pivot(sum(result)
             for rn in (p1,p2,p3,p4,p5)
            ) as p
 order by leagueid
         ,playerid
;

drop table #Leagues;
drop table #Players;
drop table #Results;
1 Like
7

I am not sure what sort of out come you are expecting but try this :smile:

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 NULL,
[LastName] nvarchar NULL)

CREATE TABLE [dbo].[Leagues](
[LeagueId] [int] IDENTITY(1,1) NOT NULL,
[LeagueName] nvarchar NULL)

insert into [Results]
Select
2 as [LeagueId],
1 as [Player1Id],
2 as [Player2Id],
3 as [Player1Result],
0 as [Player2Result]
Union all
Select
2 as [LeagueId],
1 as [Player1Id],
3 as [Player2Id],
0 as [Player1Result],
3 as [Player2Result]
Union all
Select
2 as [LeagueId],
1 as [Player1Id],
4 as [Player2Id],
1 as [Player1Result],
3 as [Player2Result]
Union all
Select
2 as [LeagueId],
1 as [Player1Id],
5 as [Player2Id],
2 as [Player1Result],
3 as [Player2Result]

insert into [Players]
select 01 as[UserId],'John' as [FirstName],'King' as[LastName]
Union all
select 02 as[UserId],'Mark' as [FirstName],'Steven' as[LastName]
Union all
select 03 as[UserId],'Peter' as [FirstName],'Griffin' as[LastName]
Union all
select 04 as[UserId],'Martin' as [FirstName],'Poole' as[LastName]
Union all
select 05 as[UserId],'Paul' as [FirstName],'Phinix' as[LastName]

insert into [Leagues]
select 'La Liga' as [LeagueName]
union all
select 'Serie A' as [LeagueName]
union all
select 'Fisrt Class' as [LeagueName]
union all
select 'XXX' as [LeagueName]
union all
select 'F Knows' as [LeagueName]
union all
select 'TTK' as [LeagueName]

--select * from [Results]
--select * from [Players]
--select * from [Leagues]

--drop table #temp

select
LeagueName,
P1.FirstName as Player1,
P2.Firstname as Player2,
Player1Result,
Player2Result
into #temp
from [Results] R
Left join [Players] P1 on R.Player1Id = p1.PlayerId
Left join [Players] P2 on R.Player2Id = p2.PlayerId
Left join [Leagues] L on R.LeagueId = L.LeagueId

--Solution--------------------------

Declare
@col as nvarchar(max),
@sql as nvarchar(max)

set @col =

stuff((
select N','+quotename(y) as [text()] from
(select distinct Player2 as y from #temp) y
order by y
for xml path ('')),1,1,N'')

set @sql =

N'select * from #temp pivot(
sum(Player2Result) for Player2 in ('+@col+')) P';

exec sp_executesql @sql

1 Like
8

bitsmed, that helps. Thank You

9

thanks for your assistance

10

Using bitsmed's table you can use the following

CREATE TABLE #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 #Players(
[PlayerId] [int] IDENTITY(1,1) NOT NULL,
[UserId] [int] NOT NULL,
[FirstName] nvarchar NULL,
[LastName] nvarchar NULL)
;

CREATE TABLE #Leagues(
[LeagueId] [int] IDENTITY(1,1) NOT NULL,
[LeagueName] nvarchar NULL)
;

insert into #leagues(leaguename)
values ('Semifinal 1')
,('Semifinal 2')
;

insert into #players(userid,firstname,lastname)
values (1,'John','Doe')
,(2,'Mark','Doe')
,(3,'Peter','Doe')
,(4,'Martin','Doe')
,(5,'Paul','Doe')
,(6,'Lucas','Doe')
,(7,'Ethan','Doe')
,(8,'Jacob','Doe')
,(9,'Jack','Doe')
;

insert into #results(leagueid,player1id,player2id,player1result,player2result)
values (1,1,2,3,0)
,(1,1,3,0,3)
,(1,1,4,1,3)
,(1,1,5,2,3)
,(1,2,1,0,3)
,(1,2,3,1,3)
,(1,2,4,3,1)
,(1,2,5,0,3)
,(1,3,1,3,0)
,(1,3,2,3,1)
,(1,3,4,1,3)
,(1,3,5,3,0)
,(1,4,1,3,1)
,(1,4,2,1,3)
,(1,4,3,3,1)
,(1,4,5,2,3)
,(1,5,1,3,2)
,(1,5,2,3,0)
,(1,5,3,0,3)
,(1,5,4,3,2)

   ,(2,6,7,3,0)
   ,(2,6,8,0,3)
   ,(2,6,9,1,3)
   ,(2,7,6,0,3)
   ,(2,7,8,1,3)
   ,(2,7,9,3,1)
   ,(2,8,6,3,0)
   ,(2,8,7,3,1)
   ,(2,8,9,1,3)
   ,(2,9,6,3,1)
   ,(2,9,7,1,3)
   ,(2,9,8,3,1)

;

select
LeagueName,
P1.FirstName as Player1,
P2.Firstname as Player2,
Player1Result
--Player2Result
into #temp
from #Results R
Left join #Players P1 on R.Player1Id = p1.PlayerId
Left join #Players P2 on R.Player2Id = p2.PlayerId
Left join #Leagues L on R.LeagueId = L.LeagueId

Declare
@col as nvarchar(max),
@sql as nvarchar(max)

set @col =

stuff((
select N','+quotename(y) as [text()] from
(select distinct Player2 as y from #temp) y
order by y
for xml path ('')),1,1,N'')

set @sql =

N'select * from #temp pivot(
sum(Player1Result) for Player2 in ('+@col+')) P';

exec sp_executesql @sql

11

Hi

I was able to use bitmeds data and get the results
with SQL below

SELECT leaguename, 
       [p2], 
       [ethan], 
       [jack], 
       [jacob], 
       [john], 
       [lucas], 
       [mark], 
       [martin], 
       [paul], 
       [peter] 
FROM   (SELECT d.leaguename, 
               b.firstname                         AS p1, 
               c.firstname                         AS p2, 
               Cast (a.player1result AS VARCHAR) + '-' + Cast (a.player2result AS VARCHAR) AS result 
        FROM   #results AS a 
               INNER JOIN #players AS b 
                       ON a.player1id = b.playerid 
               INNER JOIN #players AS c 
                       ON a.player2id = c.playerid 
               INNER JOIN #leagues AS d 
                       ON a.leagueid = d.leagueid) AS a 
       PIVOT (Max (result) 
             FOR [p1] IN ([Ethan], 
                          [Jack], 
                          [Jacob], 
                          [John], 
                          [Lucas], 
                          [Mark], 
                          [Martin], 
                          [Paul], 
                          [Peter])) AS p 
ORDER  BY 1;

Untitled.jpg991×377 189 KB

-- here's the sample data script

/*

DROP TABLE #results;

CREATE TABLE #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
);

DROP TABLE #players;

CREATE TABLE #players
(
    [playerid]  INT            IDENTITY (1, 1) NOT NULL,
    [userid]    INT            NOT NULL,
    [firstname] NVARCHAR (150) NULL,
    [lastname]  NVARCHAR (150) NULL
);

DROP TABLE #leagues;

CREATE TABLE #leagues
(
    [leagueid]   INT           IDENTITY (1, 1) NOT NULL,
    [leaguename] NVARCHAR (50) NULL
);

INSERT  INTO #leagues (leaguename)
VALUES               
('Semifinal 1'),
('Semifinal 2');

INSERT  INTO #players (userid, firstname, lastname)
VALUES               
(1, 'John', 'Doe'),
(2, 'Mark', 'Doe'),
(3, 'Peter', 'Doe'),
(4, 'Martin', 'Doe'),
(5, 'Paul', 'Doe'),
(6, 'Lucas', 'Doe'),
(7, 'Ethan', 'Doe'),
(8, 'Jacob', 'Doe'),
(9, 'Jack', 'Doe');

INSERT  INTO #results (leagueid, player1id, player2id, player1result, player2result)
VALUES               
(1, 1, 2, 3, 0),
(1, 1, 3, 0, 3),
(1, 1, 4, 1, 3),
(1, 1, 5, 2, 3),
(1, 2, 1, 0, 3),
(1, 2, 3, 1, 3),
(1, 2, 4, 3, 1),
(1, 2, 5, 0, 3),
(1, 3, 1, 3, 0),
(1, 3, 2, 3, 1),
(1, 3, 4, 1, 3),
(1, 3, 5, 3, 0),
(1, 4, 1, 3, 1),
(1, 4, 2, 1, 3),
(1, 4, 3, 3, 1),
(1, 4, 5, 2, 3),
(1, 5, 1, 3, 2),
(1, 5, 2, 3, 0),
(1, 5, 3, 0, 3),
(1, 5, 4, 3, 2),
(2, 6, 7, 3, 0),
(2, 6, 8, 0, 3),
(2, 6, 9, 1, 3),
(2, 7, 6, 0, 3),
(2, 7, 8, 1, 3),
(2, 7, 9, 3, 1),
(2, 8, 6, 3, 0),
(2, 8, 7, 3, 1),
(2, 8, 9, 1, 3),
(2, 9, 6, 3, 1),
(2, 9, 7, 1, 3),
(2, 9, 8, 3, 1);

*/