Is this a Pivot-Table, or a Cross Join or What? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

Transact-SQL (2000)

Is this a Pivot-Table, or a Cross Join or What?

Author

Topic

dukey07
Starting Member

16 Posts

Posted - 2003-07-22 : 15:26:22

Okay, this is for a fantasy football app that I am tinkering with. I have a view that gets draft results that has the following structure w/sample data (abriged)

Team, DraftRoundNumber, RoundOrderNumber, PlayerPicked

'Team1', 1, 1, 'Ricky Williams'
'Team2', 1, 2, 'Kurt Warner'
'Team3', 1, 3, 'Brian Griese'
'Team4', 1, 4, 'Joe Smith'
'Team1', 2, 1, 'Barry Sanders'
'Team2', 2, 2, 'Jim Brown'
'Team3', 2, 3, 'Don Meredith'
'Team4', 2, 4, 'Bobby Layne'

So My result set needs to look like this.

Rd/Team, Team1, Team2, Team3, Team4
1,'Ricky Williams', 'Kurt Warner', 'Brian Griese', 'Joe Smith'

2,'Barry Sanders', 'Jim Brown', 'Don Meredith', 'Bobby Layne'

My League size and total amount of rounds are all static, so my initial thought was to build a temp table, and jam the records into it either round by round or team by team with updates. I'm just wondering if there is a spiffier way to do it with a single selct etc.

Thanks, John

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-22 : 16:07:14

If you have a fixed number of columns, then it's a simple cross tab:

SELECT DraftRoundNumber AS [Rd/Team],
Max(CASE Team WHEN 'Team1' THEN PlayerPicked END) AS Team1,
Max(CASE Team WHEN 'Team2' THEN PlayerPicked END) AS Team2,
Max(CASE Team WHEN 'Team3' THEN PlayerPicked END) AS Team3,
Max(CASE Team WHEN 'Team4' THEN PlayerPicked END) AS Team4
FROM myTable
GROUP BY DraftRoundNumber

If you have a changing number of pivoting columns later on, there's a procedure that will dynamically generate the SQL needed to do the cross tab:

http://www.sqlteam.com/item.asp?ItemID=2955

You'll get better performance if you hard code the pivoted columns though.

dukey07
Starting Member

16 Posts

Posted - 2003-07-22 : 16:43:10

Thanks Robvolk, about a minute after my post, I got to thinking, "Hey, instead of blindly posting my question on a board about something, why don't I actually pick up one of these great books I have bought, and read it." In this case it was Henderson's "The Guru's guide to Transact-SQL". Well darn it all if it wasn't right there all along. Your example however is tailored specifically to my needs.

Thanks Again,
John

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources