I have a table called NAMES with 2 columns - id, name. I have a bunch
of names in the table but id is null for all. I need to develop a
script that will assign a unique id to each distinct name.
Before:
ID Name
- Tom
- Tom
- Lee
- Lee
- Lee
- Jim
- Jim
After:
ID Name
1 Tom
1 Tom
2 Lee
2 Lee
2 Lee
3 Jim
3 Jim
DDL:
create table NAMES (
id int null,
name varchar(20) not null
)
insert NAMES values (null, 'Tom');
insert NAMES values (null, 'Tom');
insert NAMES values (null, 'Lee');
insert NAMES values (null, 'Lee');
insert NAMES values (null, 'Lee');
insert NAMES values (null, 'Jim');
insert NAMES values (null, 'Jim');
Any help is appreciated. Thanks."Green" <subhash.daga@.gmail.com> wrote in message
news:1140881551.847258.142380@.i40g2000cwc.googlegroups.com...
>I have a table called NAMES with 2 columns - id, name. I have a bunch
> of names in the table but id is null for all. I need to develop a
> script that will assign a unique id to each distinct name.
> Before:
> ID Name
> - Tom
> - Tom
> - Lee
> - Lee
> - Lee
> - Jim
> - Jim
> After:
> ID Name
> 1 Tom
> 1 Tom
> 2 Lee
> 2 Lee
> 2 Lee
> 3 Jim
> 3 Jim
> DDL:
> create table NAMES (
> id int null,
> name varchar(20) not null
> )
> insert NAMES values (null, 'Tom');
> insert NAMES values (null, 'Tom');
> insert NAMES values (null, 'Lee');
> insert NAMES values (null, 'Lee');
> insert NAMES values (null, 'Lee');
> insert NAMES values (null, 'Jim');
> insert NAMES values (null, 'Jim');
> Any help is appreciated. Thanks.
>
What's the point of duplicating the names? Try:
CREATE TABLE names2 (
id INTEGER NOT NULL
CONSTRAINT pk_names2 PRIMARY KEY,
name varchar(20) NOT NULL
CONSTRAINT ak1_names2 UNIQUE);
INSERT INTO names2 (id, name)
SELECT COUNT(DISTINCT N2.name), N1.name
FROM names AS N1
JOIN names AS N2
ON N1.name <= N2.name
GROUP BY N1.name ;
Result:
id name
-- --
1 Tom
2 Lee
3 Jim
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks. In reality, the table has much more columns and already has an
existing primary key. The ID column described above is not apart of the
pk.
Any other solutions please. I'd like to avoid using cursors.
Thanks.|||Green wrote:
> Thanks. In reality, the table has much more columns and already has an
> existing primary key. The ID column described above is not apart of
> the pk.
> Any other solutions please. I'd like to avoid using cursors.
> Thanks.
Get a list of the unique name values and put them in a temp table with
an int idenitity column. Update the original table from the temp table:
Create Table names (id int null, name varchar(50))
go
insert names values (null, 'Tom');
insert names values (null, 'Tom');
insert names values (null, 'Lee');
insert names values (null, 'Lee');
insert names values (null, 'Lee');
insert names values (null, 'Jim');
insert names values (null, 'Jim');
go
Create Table #names (id int identity not null, name varchar(50))
go
insert into #names (
name )
Select distinct name from names
go
select * from #Names
go
Update names
Set names.id = t.id
from #names t
where names.name = t.name
go
select * from names
go
drop table #names
go
David Gugick - SQL Server MVP
Quest Software|||> Thanks. In reality, the table has much more columns and already has an
> existing primary key. The ID column described above is not apart of the
> pk.
In that case adding the ID column based on the name would create a
transitive dependency in violation of the standard Normal Forms. Do I take
it that you really want to put name into a related table? Accurate DDL would
be a help here.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||In sql 2005 you can do
declare @.NAMES table (
id int null,
name varchar(20) not null
)
insert @.NAMES values (null, 'Tom');
insert @.NAMES values (null, 'Tom');
insert @.NAMES values (null, 'Lee');
insert @.NAMES values (null, 'Lee');
insert @.NAMES values (null, 'Lee');
insert @.NAMES values (null, 'Jim');
insert @.NAMES values (null, 'Jim');
select *
,dense_rank() OVER( order by name)
FROM @.NAMES n1
Result:
id name
-- -- --
NULL Jim 1
NULL Jim 1
NULL Lee 2
NULL Lee 2
NULL Lee 2
NULL Tom 3
NULL Tom 3
farmer
"Green" <subhash.daga@.gmail.com> wrote in message
news:1140881551.847258.142380@.i40g2000cwc.googlegroups.com...
>I have a table called NAMES with 2 columns - id, name. I have a bunch
> of names in the table but id is null for all. I need to develop a
> script that will assign a unique id to each distinct name.
> Before:
> ID Name
> - Tom
> - Tom
> - Lee
> - Lee
> - Lee
> - Jim
> - Jim
> After:
> ID Name
> 1 Tom
> 1 Tom
> 2 Lee
> 2 Lee
> 2 Lee
> 3 Jim
> 3 Jim
> DDL:
> create table NAMES (
> id int null,
> name varchar(20) not null
> )
> insert NAMES values (null, 'Tom');
> insert NAMES values (null, 'Tom');
> insert NAMES values (null, 'Lee');
> insert NAMES values (null, 'Lee');
> insert NAMES values (null, 'Lee');
> insert NAMES values (null, 'Jim');
> insert NAMES values (null, 'Jim');
> Any help is appreciated. Thanks.
>|||to get exact result like yours
select *
,dense_rank() OVER( order by name desc)
FROM @.NAMES n1
"Farmer" <someone@.somewhere.com> wrote in message
news:%23TiCAqkOGHA.140@.TK2MSFTNGP12.phx.gbl...
> In sql 2005 you can do
> declare @.NAMES table (
> id int null,
> name varchar(20) not null
> )
> insert @.NAMES values (null, 'Tom');
> insert @.NAMES values (null, 'Tom');
> insert @.NAMES values (null, 'Lee');
> insert @.NAMES values (null, 'Lee');
> insert @.NAMES values (null, 'Lee');
> insert @.NAMES values (null, 'Jim');
> insert @.NAMES values (null, 'Jim');
>
> select *
> ,dense_rank() OVER( order by name)
> FROM @.NAMES n1
>
> Result:
> id name
> -- -- --
> NULL Jim 1
> NULL Jim 1
> NULL Lee 2
> NULL Lee 2
> NULL Lee 2
> NULL Tom 3
> NULL Tom 3
>
> farmer
>
> "Green" <subhash.daga@.gmail.com> wrote in message
> news:1140881551.847258.142380@.i40g2000cwc.googlegroups.com...
>|||Thanks all. I used Gugick's suggestion and that worked well. I find
Farmer's solution quite interesting. Maybe I'll try that nexttime.
Thanks.
