Querying unconnected tables

Question

Querying unconnected tables

bc_ed

SSC Veteran

Points: 240
More actions
May 28, 2009 at 8:47 am

#138169

I think this will be a simple solution, but I am not seeing it yet.
I have a football pool learning project I'm working on, and am trying to display overall stats for wins/losses for 21 weeks on one page.
I would like to display the info like this:
Week 1 User1Wins User1Losses User2Wins User2Losses User3Wins User3Losses
Week 2 etc
I have a query that returns correct info, but it returns it by individual user, like this:
Week 1 User1Wins User1Losses
|
Week 21
Week 1 User2Wins User2Losses
|
Week 21
Week 1 User3Wins User3Losses
|
Week 21
The query is:
select w.WeekName, dbo.udf_RETURN_Wins(w.WeekID, e.ExpertID) as Wins, dbo.udf_RETURN_Losses(w.WeekID, e.ExpertID) as Losses
from Weeks w, Experts e
How do I structure the query so it returns the info in the desired format? If more info about tables are needed, or if more clarity in explanation is needed, let me know.
Thanks in advance for your suggestions!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

What you are asking is called "Cross Tab" or "Pivot" query, there are many articles on this forum that explains how to write these kind of queries.

Have a look at the following articles by Jeff Moden

http://www.sqlservercentral.com/articles/T-SQL/63681/%5B/url%5D

http://www.sqlservercentral.com/articles/cross+tab/65048/%5B/url%5D

--Ramesh

bc_ed SSC Veteran Points: 240 More actions · Answer 2

Thanks, Ramesh.

I dumped the info I need to work with into a temp table to work with and managed to get a pivot query working that returns the info in the tabular format I want.

I am going to build a stored procedure now that does all the steps and returns the result set to my code.

Never created a pivot query before, so that wasn't really in my tool set. Are points awarded for suggesting the solution?

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

I am glad that you worked it out very well, once you had all in things in place, post back the working code to see if everything is okay. And points are awarded for each post you post.

--Ramesh