How to make string keys unique by adding numeric suffix?

Question

I have a table with two columns n integer and s varchar. n is the primary key. s is mostly unique but not always. For example

n   s
1   New York
2   Moscow
3   Paris
4   London
5   Moscow
6   Berlin
7   Moscow

I want to create another table with the same structure, the same number of rows except that s will be made unique by adding numeric suffix to second and further occurences. Example:

n   s
1   New York
2   Moscow
3   Paris
4   London
5   Moscow 2
6   Berlin
7   Moscow 3

In a single SQL command in a platform independent way (sqlite is my database). Any ideas?

Answer 1

SELECT e.ID, e.Name, (SELECT COUNT(*) FROM Table1 as d WHERE d.ID < e.ID AND d.Name = e.Name) FROM Table1 as e;

Answer 2

why not just always add the primary key value to the beginning (or end) of the string? Then you can always sub string the string by the length of the primary key so you can always get the exact string you want.

so your data could look like:

n   s
1   1New York
2   2Moscow
3   3Paris
4   4London
5   5Moscow
6   6Berlin
7   7Moscow
.
.
.
100995 100995Paris

Answer 3

You haven't posted the real problem here and my guess is that there is a MUCH better solution to that then what you want to do. That being said, the code below should give you what you want. Use it at your own peril.

I've been using MS SQL almost exclusively lately, but I think that this is mostly ANSI compatible. The exception would be the RTRIM function. There is a TRIM function in the ANSI standards, but that doesn't seem to be supported in Transact-SQL, which uses RTRIM and LTRIM.

SELECT
     n,
     RTRIM(s + ' ' + COALESCE
     (
          (
          SELECT
               NULLIF(CAST(COUNT(*) AS VARCHAR(10)), 0) AS cnt
          FROM
               Some_Table T2
          WHERE
               T2.s = T1.s AND
               T2.n < T1.n
          ), ''
     )) AS s
FROM
     Some_Table T1

P.S. - If those are your real column names, your naming convention for the columns is horrible. ;)