mapping columns - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

mapping columns

Author

Topic

goligol
Posting Yak Master

128 Posts

Posted - 2011-04-29 : 11:18:42

Hi,
I have a origin coulumn in one table and destination column in another table. They are saved in different format I would like to map them in the case that I have the same format for both of them as the originis and destinations nodes are same.
My destination is in the following format:
geo_dest geo_dest_description
001 Total
010 Alabama
011 Birmingham-Hoover-Cullman, AL CSA
012 Mobile-Daphne-Fairhope, AL CSA
160 Idaho
170 Illinois
171 Chicago-Naperville-Michigan City, IL-IN-WI CSA (IL Part)
172 St. Louis-St. Charles-Farmington, MO-IL CSA (IL Part)
181 Chicago-Naperville-Michigan City, IL-IN-WI CSA (IN Part)
....
and origins:
geo_orig geo_orig_description
01000US Total
04000US01 Alabama
04000US02 Alaska
04000US04 Arizona
04000US05 Arkansas
04000US26 Michigan
04000US27 Minnesota
04000US28 Mississippi
04000US29 Missouri
04000US30 Montana
04000US31 Nebraska
04000US32 Nevada
04000US33 New Hampshire
04000US34 New Jersey
04000US35 New Mexico
04000US36 New York
04000US37 North Carolina
04000US38 North Dakota
04000US39 Ohio
04000US40 Oklahoma
04000US41 Oregon
04000US42 Pennsylvania
04000US44 Rhode Island
04000US45 South Carolina
04000US46 South Dakota
04000US47 Tennessee
04000US48 Texas
04000US49 Utah
04000US50 Vermont
04000US51 Virginia
04000US53 Washington
04000US54 West Virginia
04000US55 Wisconsin
04000US56 Wyoming
04000US60 American Samoa
04000US66 Guam
E3300US1717600000 Chicago-Naperville-Michigan City, IL-IN-WI
E3300US1747600000 St. Louis-St. Charles-Farmington, MO-IL CFS
for example geo_dest "001" is "01000US" and "171" represents "E3300US1717600000",
My questions are:
1-how to map geo_dest to be consistent with geo_orig in terms of format
2- in the following table that I am having how can I map the right ori_dest with the one I have created from the result of first question:
geo_dest geo_orig val
010 E3300US0999999999 3
010 E3300US1154847900 2
010 E3300US1242200000 418
010 E3300US1299927260 1378
010 E3300US1299933100 709
010 E3300US1299945300 626
011 04000US21 761
011 04000US22 522
011 04000US23 14
011 04000US24 407
012 04000US02 NULL
012 04000US04 39
012 04000US05 171
012 04000US06 1118
019 04000US18 3194
019 04000US19 766
019 04000US20 423
019 04000US21 2120
020 04000US39 353
020 04000US40 29
020 04000US41 893
020 04000US42 170
020 E3300US0940800000 8
040 04000US11 2
040 04000US12 2034
040 04000US13 1475
040 04000US15 10
041 04000US26 1959
041 04000US27 1850
041 04000US28 444
041 04000US44 108

Rosembm1
Starting Member

15 Posts

Posted - 2011-05-03 : 12:12:36

Hi,
I will humbly yield to the SQL programmers with more expertise than I but I have encountered this before.
Here is the approach:
When you join the tables use functions with the ON
for 010 use the cast left and substring functions characters you want to join the linking fields.

MWRosenblatt
Spins Yak

goligol
Posting Yak Master

128 Posts

Posted - 2011-05-03 : 14:54:49

Thank you

Rosembm1
Starting Member

15 Posts

Posted - 2011-05-06 : 08:22:10

Did you get it to work?

MWRosenblatt
Spins Yak

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources