sql query group - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql query group

Author

Topic

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-09-16 : 05:51:20

hi i need a sql query to do this following

i have 2 tables
Teams - teamid, teamname
Players - playername, teamid

i need to loop through the teams getting the TeamName then under that i need to list all the playerName for each team

so like

teamA
NAME1
NAME2
NAME3

teamB
NAME1
NAME2
NAME3

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-16 : 05:55:18

[code]SELECT d.Name
FROM (
SELECT TeamName AS Name,
TeamID,
1 AS Header
FROM Teams

UNION ALL

SELECT PlayerName,
TeamID,
0
FROM Players

UNION ALL

SELECT '',
TeamID,
-1
FROM Teams
) AS d
ORDER BY d.TeamID,
d.Header DESC,
d.Name[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 06:12:12

quote:
Originally posted by craigmacca

hi i need a sql query to do this following

i have 2 tables
Teams - teamid, teamname
Players - playername, teamid

i need to loop through the teams getting the TeamName then under that i need to list all the playerName for each team

so like

teamA
NAME1
NAME2
NAME3

teamB
NAME1
NAME2
NAME3

is this for reporting purpose? If so, you can very easily get this format at your front end and you can jsut retrieve data flattened from tables using simple join query like

SELECT teamname,playername
FROM Teams t
INER JOIN Players p
ON p.teamid=t.teamid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources