Replace table fields with unique numbers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Replace table fields with unique numbers

Author

Topic

sql9999
Starting Member

2 Posts

Posted - 2007-11-05 : 10:30:25

Hi

I am trying to replace contents of all columns field contents with unique members. Can you share your thoughts on how this can be achieved?

e.g. if the table contains
John, Lewis, London, 200, 500
David, Jones, New York, 500, 400

with
2,2,1,200,500
1,1,2,500,400
And in a separate table, I need
0,1,John
0,2,David
1,1,Jones
1,2,Lewis,
2,1,London
2,2 New York

Thanks
Bobby

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-05 : 10:45:33

don't understand. Can you explain how to get from


e.g. if the table contains
John, Lewis, London, 200, 500
David, Jones, New York, 500, 400

with
2,2,1,200,500
1,1,2,500,400


0,1,John
0,2,David
1,1,Jones
1,2,Lewis,
2,1,London
2,2 New York

Please also post your table DDL

KH
[spoiler]Time is always against us[/spoiler]

sql9999
Starting Member

2 Posts

Posted - 2007-11-05 : 10:54:47

Thanks for your quick reply.

If you sort each column in ascending order you will find this order.
0,1,David ( 0 - Column Number, 1 - Unique Number and then description)
0,2,John
1,1,Jones
1,2,Lewis,
2,1,London
2,2 New York

Once the unique numbers are obtained, I need to replace the table contents with the unique numbers.. i.e. changing the current table from

John, Lewis, London, 200, 500
David, Jones, New York, 500, 400

to

2,2,1,200,500
1,1,2,500,400

This is an imaginary table at the moment, so there is no DDL. Strings are just NVARCHAR (255)..

Will H
Yak Posting Veteran

56 Posts

Posted - 2007-11-05 : 13:05:18

I think all you need is an update command. You'll have to join each column multiple times, though.

Table Test1:
id1 id2 Descr
0 1 David
0 2 Jones
1 1 John
1 2 Lewis
2 1 London
2 2 New York

Table Test2:
Text1 Text2 Text3
John Lewis London
David Jones New York

UPDATE f
SET f.Text1 = g1.Id2, f.Text2 = g2.Id2, f.Text3 = g3.Id2
FROM test2 f
JOIN test1 g1 on f.Text1 = g1.Descr
JOIN test1 g2 on f.Text2 = g2.Descr
JOIN test1 g3 on f.Text3 = g3.Descr

Of course, this means that you'd be storing ints in varchar format...

---------------------------------------------------------
SSRS Kills Kittens.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-05 : 19:59:37

quote:
This is an imaginary table at the moment, so there is no DDL. Strings are just NVARCHAR (255)

Then give us a temp table with the imaginary structure and the imaginary sample data and imaginary expected output.

see http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

KH
[spoiler]Time is always against us[/spoiler]

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-11-06 : 09:20:27

And Imagine normalizing the data first.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources