Initial data pull and combine - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Initial data pull and combine

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2002-02-21 : 16:06:36

I have a table with a list of offices that I am trying to assign
a corporate HQ for the data might look like this

officeid officename
12345 ABC Company
9877 ABC Company
7878 ABC Co
98387 XYZ Company
8973 XYZ Co

I know that I have problems with the officenames being inputed
differently and that is going to be tough to pull like names but,

my question is.

I want to parse the table and find all the like offices and
assign them a unique corporate id ie.

corpid officeid officename
1 12345 ABC Company
1 9877 ABC Company
1 7878 ABC Co
2 98387 XYZ Company
2 8973 XYZ Co

Any suggestions on how to get the corpid assigned.

Thanks

slow down to move faster...

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-21 : 16:20:47

I would work first on standardizing OfficeName. Once that is done, it will make adding the corporate ID much easier.

-Chad

skillile
Posting Yak Master

208 Posts

Posted - 2002-02-21 : 16:26:51

Yep, thats in the mix but lets assume the officenames
match ie.

xyz
xyz
xyz

next step would be

slow down to move faster...

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-02-21 : 16:43:28

quote:

corpid officeid officename
1 12345 ABC Company
1 9877 ABC Company
1 7878 ABC Co
2 98387 XYZ Company
2 8973 XYZ Co

-- create the new table with ids
create table1 (corpid int, officeid int, officename varchar(50))
-- insert the old data while assigning a dummy 0 value to corpid
insert table1
select corpid=0, officeid, officename from originalTable

declare @i int, @officename varchar(50)
select @i=1

-- loop through rows with dummy values
while exists (select * from table1 where corpid=0)
begin

-- grab next blank officename
select @officename=officename from table1 where corpid=0
-- assign corpid
update table1
set corpid=@i
where officename = @officename
-- iterate
select @i=@i+1

end

jbkayne
Posting Yak Master

100 Posts

Posted - 2002-02-21 : 16:49:15

Or do it without a cursor...

update Company set [Id] = Result.[ID]
from
Company
inner join (select
OfficeName
, (select Count(*) from (
select distinct
OfficeName
from
Company
) as Company where Company.OfficeName <= Cmp.OfficeName) as [ID]
from
(
select distinct
OfficeName
from
Company
) as Cmp
)
as Result
on Result.OfficeName = Company.OfficeName

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-02-22 : 00:20:01

It's not technically a cursor. It's slightly faster than a cursor. But it's simple for my simple mind.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources