Group By Three Tables.

The formatting of the data would be something that should be handled by your front-end application, rather than by SQL Server. The query to return the data you require is straightforward:

declare @Sports_Name TABLE (ID integer, Sport_Name varchar(250))
insert into @Sports_Name values (1, 'Football'), (2, 'Cricket'), (3, 'Hockey'), (4, 'Tennis')
declare @Teams TABLE (ID integer, Sport_ID integer, Team_Name varchar(250))
insert into @Teams values (101, 1, 'Manchester United'), (103, 2, 'Australia'), (104, 2, 'India'), (109, 1, 'Real Madrid'), (110, 3, 'New Zealand')
declare @Player_Name TABLE (ID integer, Team_ID integer, Player_Name varchar(250))
insert into @Player_Name values (1, 101, 'Rooney'), (2, 104, 'Tendulkar'), (3, 103, 'Ponting'), (4, 109, 'Ronaldo'), (5, 101, 'Van Persie'), (6, 110, 'Simond')

-- OK, so the above query lines create tables for me to replicate your environment.
-- The query below is the actual JOIN and ORDER that returns the data your 
--         client app can format.

SELECT Sport_Name, Team_Name, Player_Name
FROM @Sports_Name s LEFT JOIN @Teams t on s.ID = t.Sport_ID
LEFT JOIN @Player_Name p ON t.ID = p.Team_ID
Order by s.ID