Enumerar Registros En Sql Server
En ocasiones tenemos la necesidad de enumerar registros en SQL Server, por ejemplo si un dato se repite varias veces queremos obtener la numeracion consecutivaque lleva:
|Datos |
| A |
| A |
| A |
| B |
| B |
Queremos recibir:
|Valor |Enumeración |
|A | 1 |
|A| 2 |
|A | 3 |
|B | 1 |
|B | 2 |
Aquí veremos dos formas de hacer esto.
Ejemplos:1. Utilizando row_number() y partition (FORMA RECOMENDADA):
Declare @t1 Table (c char(1))
Insert Into @t1 values('A')
Insert Into @t1 values('A')
Insert Into @t1 values('A')Insert Into @t1 values('A')
Insert Into @t1 values('B')
Insert Into @t1 values('B')
Insert Into @t1 values('B')
Insert Into @t1 values('B')
Insert Into @t1 values('C')
Insert Into @t1 values('C')Insert Into @t1 values('C')
Select Valor = c,
Enumeracin = row_number() over (partition by c order by c)
from @t1
2. Utilizando un Cursor:
declare @t1 table (c char(1))declare @t2 table (n int, c char(1))
insert into @t1 values('A')
insert into @t1 values('A')
insert into @t1 values('A')
insert into @t1 values('A')
insert into @t1 values('B')
insert into @t1 values('B')insert into @t1 values('B')
insert into @t1 values('B')
insert into @t1 values('C')
insert into @t1 values('C')
insert into @t1 values('C')
declare @c char(1), @aux char(1)
declare @n int
declare p cursor for
select c
from @t1
open p;
fetch next from p into @c
while @@FETCH_STATUS = 0
begin
if(@c = @aux)
begin
set @n =
(select MAX(n) + 1 from @t2 where c = @c)insert into @t2 values(@n, @c);
end
else
begin
insert into @t2 values(1, @c);
end
set @aux = @c
fetch next from p into @c
end
close p;
deallocate p;
select *
from @t2
...
Regístrate para leer el documento completo.