A subquery question 3

[Zoom1177]

Hi,

lets say i got this ONE table

Name________Sport
=================
Brian Soccer
Karen Golf
Barb Basketball
Terry Soccer
Mona Golf
Tim Basketball
Jack Golf

I want in ONE resultset to get the following

Soccer
=====
brian
terry

Basketball
==========
tim
barb

Golf
======
karen
mona
jack

What is the easiest way to do that using T-SQL?

Thanks

 

[Catadmin]

I would advise looking into temporary tables and / or the UNION / UNION ALL operators in Books Online.

Honestly, though, it looks like you want to use a reporting tool more than you want to use a T-SQL query since T-SQL results don't tend to format a single result set in the manner in which you're posting it. That's really 3 result sets, not 1.

However, you could write up a stored procedure with three different result sets and the single SP would list all three result sets just like that.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[AlexCuse]

Probably using the CASE statement, but that will give nulls in your rows that are used as placeholders.

Where are you trying to display this query result, and do you want to show only one column (sport) at a time or all three side by side?

A wise man once said
"The only thing normal about database guys is their tables".

 

[Zoom1177]

I am trying to display it in a web application by having the head of the table would be the kind of sport and underneath it rows of people belong belong to that sport (for example)

It seems to me that in most cases you want to return your table as a whole and write your filtering/sorting/grouping code in the fron-end (ASP)

Thanks anyway.

 

[Catadmin]

You are correct. Formatting like this should be in the front end. However, like I said, you could do a stored procedure sort of like:

Create Procedure SportsList
As

Select SportsName, PlayerName
from Sports
where SportsName = 'Basketball'

SportsName, PlayerName
from Sports
where SportsName = 'Soccer'

SportsName, PlayerName
from Sports
where SportsName = 'Golf'

GO

Which comes close to what you want and just needs fine tuning on the front end.




Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

Ack! My cut-n-paste left out the SELECT statement. The proper code would be like:

Create Procedure SportsList
As

Select SportsName, PlayerName
from Sports
where SportsName = 'Basketball'

[b]Select[/b] SportsName, PlayerName
from Sports
where SportsName = 'Soccer'

[b]Select[/b] SportsName, PlayerName
from Sports
where SportsName = 'Golf'

GO

Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[AccessUser22]

But then what happens when you add a new sport? You'd have to go through and change your actual code to include the new sport. This doesn't seem efficient to me.

 

[Zoom1177]

Thanks Catadmin and AlexCuse for your help.

Accessuser22, you're right for sure, it would be limited only to the 3 sports.

Thanks all for your help.

 

[Catadmin]

True enough. But you could do a WHILE loop with a counter that's based on the # sports, uploads the appropriate recordset to a temp table (based on sports name) and then does a SELECT statement on all the Temp tables with a UNION. Put that in a Proc and you don't have to worry about only have 3 sports.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Zoom1177]

Catadmin,

thanks i'll try that.

 

[AccessUser22]

Yah, I think the loop is your best bet for further enhancements. That's the way I would go. Saves time down the road.

 

[Zoom1177]

Thanks all i found a way to know how many sports are they in the table before i start the loop because users will add more sports down the road

here it is

declare @var1 int

select distinct sport
from sport
set @var1= @@rowcount
print @var1

There is no need to use "Print" i just used it so i can see it in Query Analyzer.

So i guess now that i got the actual number of sports on the table, it's not a static loop anymore and i can start looping from there, ha?

Any idea how i can take it from there would be very appreciated.

The result that i want - again- as i posted in the first post, and must be in one View, or SP or temp table is as following

Soccer
=====
brian
terry

Basketball
==========
tim
barb

Golf
======
karen
mone
jack

thanks all

 

[Catadmin]

I wrote a loop for concatenation purposes in the following FAQ (FAQ183-6466). You'll have to alter it to create temp tables for storing your individual sports in, but it should get you started.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[AlexCuse]

Yeah that is probably a bit out of the scope of what anyone here will have time to write for you. Cat's loop is very good, and I will throw in a couple of extra hints.

First step - Create a temp table with all of your indicidual sports.

Second step - set up your loop, with some dynamic SQL utilizing the CASE statements stored above. Some info on Dynamic SQL here :

http://www.sommarskog.se/dynamic_sql.html

Your dynamic select statement will be used to insert into your temp tables (also created dynamically - It will help to include an ID field.)

-hint - put a WHERE clause on your select statement to exclude everything where sport <> desired sport. This way you have no nulls or gaps in ID Number.

Step three - create a numbers table that has numbers from 1 to (max value of ID field in ANY of your tables)

info on numbers tables here (you might need to sign up to read it)

http://www.sqlservercentral.com/columnists/mcoles/2547.asp

Step four - use a query like this to get your final recordset (this will also need to be created on the fly as you will have varying numbers of sports)

select #Soccer.Name, #Basketball.Name, #Golf.Name
from #Numbers n left join #Soccer on n.[ID] = #Soccer.[ID]
left join #Basketball on n.[ID] = #Basketball.[ID]
left join #Golf on n.[ID] = #Golf.[ID]

This is seems like a lot, but I think you will be able to get it done. Let us know what you come up with.

Hope this helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[Zoom1177]

Catadmin,

Thanks for the link, it helps a lot

AlexCuse,

I understand that no one have time and i dont want anyone to write for me.

Some people appreciate examples, i posted some of my code in the same question in ASP as i said there (not here as it's not will be usefull here) i am creating a tree menu in my page and all the ideas head toward creating recursive loops to filter that one table into many branches of my tree would only be done on the front-end...howerver, i still like to find a way to return back from the server more filtered recordset than just the whole table.

Thanks for your hints/elaboration anyway.

 

[r937]

I am trying to display it in a web application by having the head of the table would be the kind of sport and underneath it rows of people

every web application has a scripting language (you said you were using ASP), so do the formatting there


use this query --

select sport,name
  from daTable
order by sport,name

then detect the control breaks with your application scripting language

this keeps the sql portion of the app easily maintainable (actually, zero maintenance), while keeping the web scripting part of the application minimal (and also zero maintenance)

some things are just not meant to be done with sql




r937.com | rudy.ca

 

[AlexCuse]

ME72 - I did not mean to imply that you were looking for someone to write for you, I just wanted to make it clear that this process would not be as easy as it perhaps 'should' be. I apologize for making it sound like that.

What I've laid out is not going to return the whole table.

It will return a recordset like this, which I thought was what you wanted:

Golf Basketball Soccer
SomeName SomeName SomeName
AnotherName Null Another Name
YetAnotherName Null Null


If this is not what you were looking for, can you please elaborate on what you mean by 'more filtered'?

If you want only to show one sport per page then your job becomes much much easier.

Good Luck,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[Zoom1177]

r937,

You're absolutely right, something not meant to be done with sql. However, some habits are hard to drop.

Yes i am using both Javascript and VBscript. And you're right the script will be able to break the table to what i need. Thanks for your hellp.

AlexCuse,

No need to apologize at all, no harm done. and thank you really for your elaboration it was helpful.

Oh i wish it was one category per page man! but it's okay it's good experience that i am learning from it by the minute.

Thank all for your help
Great Place Tek-Tips and good people

 

[Catadmin]

If you dynamically create a temp table for each sport within the WHILE loop and then do a SELECT on the temp table still within the WHILE loop (don't forget to drop the table too), you'll get a similar effect to what you originally posted. The only issue is that the SELECTS won't "print" until the entire looping process is done.

Although, you're going to want to check performance for my above suggestion against what the ASP can do. It might be better performance-wise to do this on app server.


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Zoom1177]

Catadmin,

I can tell you right now that it will kill the performance, because the sports table is just an example, what i have actually (in one table) is a 4 tier category(parent/child/child/child) and i have 5 different parents!!

and if those were the end of it i would've just created a static tree menu, but those categories will increase in the future...the best and easiest way -as every one said here and in the ASP forum- is to break them down with a good script

I am just a stubborn guy i guess, cause the harder the script looks the more i keep saying hmmmm if i could just pull them filtered from a stored procedure or something

Thanks all for your help