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 |
|
buttonjockey
Starting Member
1 Post |
 Posted - 2012-01-06 : 12:05:59
|
| Hi FolksI want to generate 3 columns of (new) data for [Territory], [Country] and [Region] in a query based on 4 or more text fields in a large table (10,000,000+ rows). A combination of two columns [Peril] (e.g. Earthquake 'EQ' or Flood 'FL') and [Region] (e.g. North America 'NA' or Europe 'EU') gives 72 unique combinations and for each possibility the logic for creating the 3 new columns is different. Other than an horrific CASE statement, can anyone think of doing this a better way? I thought of storing the logic for each new field for each of the 72 combinations as a text string in a lookup table, but can't think of a way to execute this text in the query. Any help appreciated...P.S. As a newbie to SQL Team, i can't tell you how helpful this site has been to my SQL development over the last 2 years, so thanks everyone....just another cog in the machine |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-01-06 : 12:18:32
|
| Without the basics (DDL, DML and expected output) it's hard to offer much. One suggestion might be to create some sort of mapping table that maps Peril and Region to three new columns and then join to that. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
Complex CASE statements