Post reply

Creating a count field in DTS/T-SQL.

  • July 10, 2003 at 4:10 am

    #160144

    I need to update a field in a table with an id (or count of) values in another field. For example I have a field with a postcode/zip code in it, however there are multiple zip codes on the file. For the first zip code I need to put a 1 in this field, the second record a 2 and so on. Is there any way I can do this in a DTS T-SQL script? Or any other DTS task for that matter. I'm fairly new to SQL server and as this seems not to be an obvious SQL type process I'm not sure how to go about it in DTS. I could do it in a language like VB, so maybe using an ActiveX script task is a way of doing this. However I find that a little alien at the moment. Any help would be greatly appreciated,

    thanks,

    Matt

  • July 10, 2003 at 7:04 am

    #464840

    Do you like this

    create table #pc (idno int,p varchar(8))

    insert into #pc values (0,'AA1 BB1')

    insert into #pc values (0,'AA1 BB1')

    insert into #pc values (0,'AA1 BB1')

    insert into #pc values (0,'AA2 BB2')

    insert into #pc values (0,'AA2 BB2')

    insert into #pc values (0,'AA2 BB2')

    insert into #pc values (0,'AA2 BB2')

    insert into #pc values (0,'AA2 BB2')

    insert into #pc values (0,'AA3 BB3')

    

    declare @idno int,@p varchar(8)

    set @idno=0

    set @p=''

    update #pc set @idno=(case when p<>@p then 1 else @idno+1 end),idno=@idno,@p=p

    select * from #pc

    

    idno        p        

    ----------- -------- 

    1           AA1 BB1

    2           AA1 BB1

    3           AA1 BB1

    1           AA2 BB2

    2           AA2 BB2

    3           AA2 BB2

    4           AA2 BB2

    5           AA2 BB2

    1           AA3 BB3

    Far away is close at hand in the images of elsewhere.
    Anon.

  • July 10, 2003 at 7:56 am

    #464858

    Many thanks, that works a treat!!!

  • July 11, 2003 at 10:06 am

    #465115

    Make sure the table is ordered by the column in question unless you want the count to reset. The UPDATE statement does not permit the order by clause. Perhaps either a clustered index (e.g., "create clustered index [IX_#pc] ON #pc (p asc))" or an updateable view (with an order by clause) can be used.

    Randy
    Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply