create pivot table using sql-CodePudding

I have a database called player.db

These database has two tables.

The tables called person and the other is called match.

person table is

Player_ID	Player	Country
1	Lionel Messi	Argentina
2	Luis Suarez	Uruguay
3	Neymar	Brazil

match table is

Match _ID	Game	Player_ID	Date	Season
1	Uruguay-Paraguay	2	5/3/2019	1
2	Uruguay-Chile	2	19/3/2019	1
3	Argentina-Chile	1	22/3/2019	1
4	Brazil-Guyana	3	3/4/2019	1
5	Brazil-USA	3	1/6/2020	2
6	Brazil-Belize	3	3/7/2020	2
7	Brazil-Suriname	3	5/7/2020	2
8	Argentina-USA	1	8/8/2020	2
9	Argentina-Canada	1	3/3/2021	3
10	Argentina-Grenada	1	8/3/2021	3
11	Uruguay-Suriname	2	7/4/2021	3
12	Uruguay-Mexico	2	2/2/2022	4
13	Uruguay-Jamaica	2	4/2/2022	4
14	Brazil-Ecuador	3	5/2/2022	4

My pivot table should look like these:

Season	Player
1	Luis Suarez
2	Neymar
3	Lionel Messi
4	Luis Suarez

I want a sql code which create a pivot table which shows which player played most with topscore in which season year. For example Luis Suarez occured most in season 1.

I started coding in sql, but got not the desired solution

SELECT Player_ID, COUNT(*)FROM match GROUP BY Player_ID HAVING COUNT(*) max

The problem is I got an error and it doesn't create a pivot table which show which player played most in which season.

CodePudding user response：

Your clause HAVING count(*) max may cause the error.

The solution may depend on the relational data base you use. Here is a solution with postgresql :

SELECT DISTINCT ON (Season)
       m.season, p.player, count(*) AS count
  FROM match AS m
INNER JOIN player AS p ON p.player_ID = m.Player_ID
 GROUP BY m.Season, p.Player_ID, p.player
 ORDER BY m.Season ASC, count DESC

see dbfiddle

CodePudding user response：

Join the tables, group by season and player to get the number of matches for each player and use FIRST_VALUE() window function to pick the top player of each season:

SELECT DISTINCT m.Season,
       FIRST_VALUE(p.Player) OVER (PARTITION BY m.Season ORDER BY COUNT(*) DESC) Player
FROM match m INNER JOIN person p
ON p.Player_ID = m.Player_ID
GROUP BY m.Season, m.Player_ID;

See the demo.