Need to create field with incremental number per group 1

[MaffewW]

greetings, wonder if anyone can help with a tricky little problem!

I need to denormalise a table, to get it into a format to load into our telephone system database. I think I can do this once I have achieved this step:

We have an excel sheet containing multiple records per customer, one for each phone number.

it looks something like this:

AccountNumber Phonenumber
000123 01273153456
000123 01273153457
000123 01273153458
000756 01546123456
000756 01546123457

What I need to try and do is add a column to give the instance of each accountnumber ie

LineNumber AccountNumber Phonenumber
1 000123 01273153456
2 000123 01273153457
3 000123 01273153458
1 000756 01546123456
2 000756 01546123457


this gives me a new number for each row, though I dont know how to get it to start new for each accountnumber

declare @linenumber int
set @linenumber = 1
update tbldataload set @linenumber= linenumber = @linenumber + 1

There are a maximum of 6 x account numbers in any one group

hope thats clear and someone may be able to help

all the best

Matt

Matt

Brighton, UK

 

[MaffewW]

I've also posted this on the Excel forum in case its easier that way

Matt

Brighton, UK

 

[JamesLean]

This should work, so long as you don't have duplicate phone numbers for an account.

SELECT (
    SELECT COUNT(*) FROM tbl
    WHERE accountnumber = t.accountnumber
      AND phonenumber <= t.phonenumber
  ) AS linenumber,
  accountnumber,
  phonenumber
FROM tbl t
ORDER BY accountnumber, linenumber

--James

 

[qsac]

--------------------------------------------------------------------------------------
-- First create the new column in the table you want. --
-- --
-- for purposes of this example --
-- --
-- Table Name: CustomerInfo --
-- LineNumber: column you created below --
-- AccountNumber: i think this is self explanatory --
-- PhoneNumber: i think this is self explanatory --
-- --
-- What I assume: --
-- 1. No account number is 0 --
-- 2. AccountNumber and PhoneNumer together can be concidered a Unique Identifier --
--------------------------------------------------------------------------------------

DECLARE AllCustomerInfo CURSOR SCROLL FOR
select AccountNumber, PhoneNumber
from CustomerInfo
order by AccountNumber

declare @AccountNumber varchar(50)
declare @CurrentAccountNumber varchar(50)
declare @PhoneNumber varchar(50)
declare @intCurrentLineNumber int

set @intCurrentLineNumber = 1
set @CurrentAccountNumber = '0'

OPEN AllCustomerInfo

FETCH NEXT FROM AllCustomerInfo INTO @AccountNumber, @PhoneNumber


WHILE @@FETCH_STATUS=0
BEGIN

if @CurrentAccountNumber = @AccountNumber
begin
update CustomerInfo set LineNumber= @intCurrentLineNumber
where AccountNumber = @AccountNumber and PhoneNumber = @PhoneNumber

set @intCurrentLineNumber = @intCurrentLineNumber + 1
end
else
begin
set @intCurrentLineNumber = 1
update CustomerInfo set LineNumber= @intCurrentLineNumber
where AccountNumber = @AccountNumber and PhoneNumber = @PhoneNumber

set @intCurrentLineNumber = @intCurrentLineNumber + 1
set @CurrentAccountNumber = @AccountNumber
end

FETCH NEXT FROM AllCustomerInfo INTO @AccountNumber, @PhoneNumber
END

CLOSE AllCustomerInfo
DEALLOCATE AllCustomerInfo

 

[MaffewW]

James, that works, superb thanks

qsac, thanks as well, i havent had chance to try yours, I need to get on to this quick now, will test it later
cheers


Matt

Brighton, UK