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.
| Author |
Topic |
|
Apples
Posting Yak Master
146 Posts |
 Posted - 2008-03-23 : 15:52:24
|
| Here's my table:--------------------------Teams--------------------------ID | Name | City | State--------------------------I want to output the Teams in this format:NameCity, StateHowever, some teams don't have a name, so I want to output them like this:City, State TeamCity, StateAnd some teams don't have a name or a city:State TeamStateAnd then there are teams who have a name, but not a city:NameStateI'd like to do this all in one select statement. Is there a way to do something like:SELECT Name IF EXISTS, City IF EXISTS, State IF EXISTSFROM TEAMS |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2008-03-23 : 18:44:39
|
| You can't have varying number of fields in one select statement. If you want a combination of Id,name,state,city then they'll all have to appear in your select list. You can't change the select list based on what is or isn't there. Does this all come from one table? List the table structure for the table(s) that you are working with, and some sample data, and we may be able to come up with something that will work for you.Jim |
 |
|
|
GhantaBro
Posting Yak Master
215 Posts |
Posted - 2008-03-23 : 18:53:06
|
| I guess something like this might workselect case when isnull(city, '') = '' then State else City end ...... u might have to write lots of case statements to havesomething like name city state or city state or name city.... |
|
|
|
Apples
Posting Yak Master
146 Posts |
Posted - 2008-03-23 : 20:07:11
|
| jimfI did show the table...let me try and explain again, I don't think I was clear.I want to select data that isn't null. If it is null, I don't select it. Here's some data...--------------------------ID | Name | City | State--------------------------1 | Falcons | Albany | New York2 | NULL | Buffalo | New York3 | NULL | NULL | Florida4 | Hawks | NULL | TexasFor that data, I'd like to list it like so:FalconsAlbany, New YorkBuffalo, New York TeamBuffalo, New YorkFlorida TeamFloridaHawksTexas |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-03-23 : 20:28:52
|
| SELECT CASE WHEN Name IS NOT NULL THEN Name + CHAR(13) END + CASE WHEN City IS NOT NULL THEN City + ',' ELSE '' END + CASE WHEN Name IS NOT NULL THEN State ELSE State + 'Team' END+ CASE WHEN Name IS NULL THEN CHAR(13) + CASE WHEN City IS NOT NULL THEN City + ',' ELSE '' END + State ENDFrom Table |
|
|
|
GhantaBro
Posting Yak Master
215 Posts |
Posted - 2008-03-23 : 20:34:31
|
| You can use COALESCE() |
|
|
|
|
|
|
|
|
Select data only if it is there