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 |
|
amason
Starting Member
7 Posts |
 Posted - 2007-09-21 : 10:49:43
|
| So I have a table that has address fields made from pulling the address out of a string in another table. I was able to separate out the street1, street2, city, state, zip data into there own columns, and I have the name column filled for each row. Right now it looks like thisName street1 street2 city state zipJoe 123maple null null null nullJoe null apt 4 null null nullJoe null null wash. null nullJoe null null null DC nullJoe null null null null 26666what I want to end up with is a table where the row for Joe would have all of that information in one row, not 5, like thisName street1 street2 city state zipJoe 123maple apt 4 wash. DC 21111How would this be done?? Thanks,Andrew |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-09-21 : 10:57:03
|
| select name, street1 = max(street1 ), street2 = max(street2 ), city = max(city ), state = max(state ), zip = max(zip)from tblgroup by name==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
amason
Starting Member
7 Posts |
Posted - 2007-09-21 : 11:03:23
|
| NICE!!!worked like a charm. What is this type of function called? I'd like to learn more.Thanks very much!! ~Andrew |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-21 : 11:26:03
|
quote:
Originally posted by amason
NICE!!!worked like a charm. What is this type of function called? I'd like to learn more.Thanks very much!! ~Andrew
He used the MAX function to return the maximum value in each column. This worked because all the other values were null.
Future guru in the making. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-21 : 11:33:47
|
"What is this type of function called?""Aggregate", but what Nigel has done is using a side effect of the MAX() function, so looking up all the other Aggregate functions may not advance your cause very much  Kristen |
|
|
|
|
|
|
|
|
combining rows