SQL Query - 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

Transact-SQL (2000)

SQL Query

Author

Topic

dupati1
Posting Yak Master

123 Posts

Posted - 2005-10-26 : 15:40:14

rob3rto
Starting Member

8 Posts

Posted - 2005-10-26 : 15:58:27

Simple concatenation would work:

Select city + ', ' + state as address

But if you have null values you'll have to make it a bit more robust else you may have unwanted commas (or nothing):

Select isnull(city, '') + isnull(', ' + upper(state) , '' )

rob3rto
Starting Member

8 Posts

Posted - 2005-10-26 : 15:59:33

Oops, I missed the numbers at the end of the state... my bad!

dupati1
Posting Yak Master

123 Posts

Posted - 2005-10-26 : 16:01:35

yeah...thats the problem i am facing...do i need to use cursors ??

rob3rto
Starting Member

8 Posts

Posted - 2005-10-26 : 16:58:56

I'm sure some of the pros here have a stream-lined non-cursor solution, but a cursor could indeed be used:

create table addrcount (ClientID numeric, Address varchar(40), City varchar(40), State varchar(40))

insert addrcount values( 1 , 'something', 'Blah', 'WV')
insert addrcount values(2, 'someblah', 'Pitt', 'PA')
insert addrcount values(2 , 'blahblah', 'Pitt', 'PA')
insert addrcount values(3 , 'other', 'Mtow', 'SC')
insert addrcount values(3 , 'someother', 'Mtow', 'SC')
insert addrcount values(3 , 'somexxxx', 'Mtow', 'SC')
insert addrcount values(3 , 'otherblah', 'Blah', 'WV')

-- begin cursor
declare @ClientID numeric, @Address varchar(40), @City varchar(40), @State varchar(40),
@max numeric, @pass numeric

declare c1 cursor for
select distinct ClientID , Address , City , State
from addrcount
order by state

open c1
fetch next from c1 into @ClientID, @Address, @City, @State
set @max = (select count(state) from addrcount where state = @state)
set @pass = 1
while @@fetch_status = 0
begin

if @pass <= @max
begin
select @ClientID, isnull(@City, '') + isnull(', ' + upper(@State) , '' ) + case when @pass = 1 then '' else cast(@pass as varchar) end, @City, @State
set @pass = @pass + 1
end --> end if

else
set @pass = 1
fetch next from c1 into @ClientID, @Address, @City, @State
end
close c1
deallocate c1

Convoluted, but works. Hopefully you can find a good solution from here. :^]

dupati1
Posting Yak Master

123 Posts

Posted - 2005-10-26 : 17:07:49

rob, thanks for your help...i also started write a stored procedure with a cursor and was on the same track...

i ran the one you suggested but it would stop numbering at 2 and wouldnt number after 2..did u see that...

for Mtow, SC in your above table

we get
Mtow, SC
Mtow, SC2

and not Mtow, SC3

rob3rto
Starting Member

8 Posts

Posted - 2005-10-26 : 17:36:21

yikes, my bad again! it was only counting the first state and not updating. this oughta fix it:

if @pass <= @max
begin
select @ClientID, isnull(@City, '') + isnull(', ' + upper(@State) , '' ) + case when @pass = 1 then '' else cast(@pass as varchar) end, @City, @State , @address
set @pass = @pass + 1
fetch next from c1 into @ClientID, @Address, @City, @State
end
else
begin
set @pass = 1
set @max = (select count(state) from addrcount where state = @state)
end

dupati1
Posting Yak Master

123 Posts

Posted - 2005-10-27 : 09:10:07

Thanks for helping me out...but your code still has some loopholes...its even numbering same city and state if they belong to different clients...

rob3rto
Starting Member

8 Posts

Posted - 2005-10-27 : 13:58:09

I see; I didn't realize the usage of the client id.

If you create a unique identifier for the table it would be much easier to group the records properly. Let me know if you can't get that work.

- rob

achobbs
Starting Member

11 Posts

Posted - 2005-10-27 : 14:20:36

I think the following type of statement would work and avoids a cursor (it uses a temp table and then a single select). I made up the columns, but you should be able to finish mapping it from there:

-- insert everything into a temp table and create an ident field
-- we will sort by id, state, city to get items in proper order
select
city,
state,
id,
identity(int,1,1) as ident
into #team
from
TestTeam
Order By
id, state, city

-- now select the items, using the current ident value
-- and subtracting the min ident value for the matching id/state
select
id,
city,
state + convert(varchar, (ident - (select min(ident) from #team tt where tt.id = t.id and tt.state = t.state)) + 1, 20)
from #team t

The select gave me the following results:

1,'Blah','WV 1'
2,'Pitt','PA 1'
2,'Pitt','PA 2'
3,'Mtow','SC 1'
3,'Mtow','SC 2'
3,'Mtow','SC 3'
3,'Blah','WV 1'

Hope this helps,

Ashton Hobbs

dupati1
Posting Yak Master

123 Posts

Posted - 2005-10-27 : 17:10:12

Thanks guys for all your valuable suggestions...everything is working fine now...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources