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 |
|
erhimc
Starting Member
8 Posts |
 Posted - 2006-01-19 : 17:39:49
|
| Hello All,Im very new to programming with SQL Server, and I need some code to do this:I have a table... a BIG table that has names, and a field for every STATE (name, ak, al, ar, az, ca...) you get the idea. I need to generate a string that will look at all of those state fields, and if they are not null fields then add them to a variable?... that can be displayed. Does that make senseSo each could have all 50, or they could have none, but the string for each would only display the fields which are not NULL..The output needs to be something like thisName, ak, caName, al, ar, az, caName, ak, az, ca...I hope Im describing that ok. none the less, I am a novice at this, and I need to do this rather quiclky, so I thought I would drop it out here to see if anybody has an idea.Thanks in Advance,Ed |
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2006-01-19 : 17:49:37
|
| SELECT COLEASCE(FirstState + ',', '') + COLEASCE(SecondState + ',', '') + ... COLEASCE(LastState, '') AS StatesFROM <Your Table>WHERE <some where clause>This will work but if the last state is null in a row then you will have a trailing comma. |
 |
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2006-01-19 : 17:51:27
|
| Also you might want to normalize this table. There should be a 1 to many relationship between your main table and the states (since 1 row in your table can belong to 0, 1, or more states). This would get rid of those ugly NULLS in your database.Dustin Michaels |
|
|
|
erhimc
Starting Member
8 Posts |
Posted - 2006-01-19 : 18:00:03
|
| Thank you for the input Dustin.I would LOVE to redo this entire database, but unfortunately I have inherited a beast, and the 'rest' of the application depends on the current poorly designed database.I have my hands full getting it cleaned up.E. |
|
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2006-01-19 : 18:13:31
|
quote:
Originally posted by erhimc
Thank you for the input Dustin.I would LOVE to redo this entire database, but unfortunately I have inherited a beast, and the 'rest' of the application depends on the current poorly designed database.I have my hands full getting it cleaned up.E.
If you only access the database thru stored procedures / views you should be able to change the database however you want. Because if you change the tables in your database you just need to change the queries in your stored procedures / views. |
|
|
|
erhimc
Starting Member
8 Posts |
Posted - 2006-01-19 : 18:15:11
|
| Dustin,When I use your code idea...I get this error message:Server: Msg 195, Level 15, State 10, Line 3'COLEASCE' is not a recognized function nameE. |
|
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2006-01-19 : 18:20:06
|
| I misspelled the function name, try COALESCE instead. |
|
|
|
erhimc
Starting Member
8 Posts |
Posted - 2006-01-19 : 18:25:48
|
quote:
Originally posted by DustinMichaels
I misspelled the function name, try COALESCE instead.
No wonder I could not find it...It creates the string, but I need it to look more like this:Name, ak, caName, al, ar, az, caName, ak, az, caSo if the field is not NULL, and the db field is CA, it needs to show up in the list as 'CA'E. |
|
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2006-01-19 : 19:18:59
|
quote:
Originally posted by erhimc
quote:
Originally posted by DustinMichaels
I misspelled the function name, try COALESCE instead.
No wonder I could not find it...It creates the string, but I need it to look more like this:Name, ak, caName, al, ar, az, caName, ak, az, caSo if the field is not NULL, and the db field is CA, it needs to show up in the list as 'CA'E.
How are you storing the values in the table are they boolean values? Maybe this would work.SELECT CASE WHEN Arkansas IS NOT NULL THEN 'ak,' ELSE '' END + CASE WHEN Arizona IS NOT NULL THEN 'az,' ELSE '' END + ... CASE WHEN Wyoming IS NOT NULL THEN 'wy' ELSE '' END AS StatesFROM <Your Table>WHERE <some where clause> |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
String Concatination Help