• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

500 marks question: Is it possible to do it by using T-SQL statement?

Dear all,

  I've the following table and would like to do the following:

  Name   Sequence
 -------------------------
   Tom     <null>
   Tom     <null>
   Tom     <null>
   John      <null>
   John     <null>
   Peter    <null>
   Ann      <null>
   
 And I would like to change the result as follow:

  Name   Sequence
 -------------------------
   Tom     1
   Tom     2
   Tom     3
   John     1
   John     2
   Peter    1
   Ann      1

  "Sequence" is genereated base on the number of "Name".  

  Does anyone tell me how can I do so?

0
LegalIT
Asked:
LegalIT
  • 3
1 Solution
 
ispalenyCommented:
select name,id=identity(int,1,1)
into #q
from YourTable
select * into YourTable2
from YourTable where 1=0
insert YourTable2(name,seq)
select T.name,T.id-M.mid+1
from #q T
join
(
select name,min(id) mid
from YourTable
group by name
) M on A.name=B.name
0
 
NetworkArchitekCommented:
Is the table actually setup like that without any other fields, as in the table has no primary key? There are some things you could do but if they do not have a primary key then I think the techniques would be much more complicated, I've never had to do something like that. If there is a primary key then it is simpler. You can have two While loops, the outer iterates through all the records, and the inner iterates through while the name is the same. So you could have three variables, one to hold the PK, one to hold the name, and one to increment while the name is the same. To be honest without a key it may just be simpler to export the data to a file and use another programtic method to do that. The way to avoid the NULL values when you insert the names, in case you haven't realized, is to create a stored procedure to enter the name, do a Count(*) of the records with the same name, and if it is greater than 0, do a Max(Sequence), and then insert the name with one greater than Max(Sequence).
0
 
LegalITAuthor Commented:
Dear NetworkArchitek

  How about the table becomes this:

ID  Name   Sequence
 -------------------------
 1  Tom     1
 2 Tom    1
 3  Tom     1
 4 John    1
 5  John    1
 6  Peter    1
 7  Ann     1

Where ID is the primary key..
0
 
ispalenyCommented:
select id,Name,NID=identity(int,1,1)
into #q
from YourTable

update A
set Sequence = T.NID - M.mid + 1
from YourTable A
join #q T on A.ID = T.ID
join
(
      select Name,min(NID) mid
      from #q
      group by Name
) M on M.Name=T.Name

drop table #q
0
 
ispalenyCommented:
Now, when you have a primary key, we are able to update your source table "YourTable". In my previous post I was filling table "YourTable2". And use

select id,Name,NID=identity(int,1,1)
into #q
from YourTable
order by Name

instead of

select id,Name,NID=identity(int,1,1)
into #q
from YourTable

0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now