Append number 2

[damipera]

Hello folks,

I've been trying to achieve a SQL result but without any success.

If I have a table that is like this:

country
-------
Spain
Greece
UK
Spain
Japan
Australia
Japan
Norway
US
France
US
Spain
.
.
.

I want to achieve a result that is something like this;

country
-------
Spain1
Greece
UK
Spain2
Japan1
Australia
Japan2
Norway
US1
France
US2
Spain3
.
.
.

Is there a way to do it? Can you please give me some ideas?

Thank you

 

[simian336]

You can use

with cte as (
select country, row_number() over (partition by ccna order by country) as cnt
from yourfile
)
select country, country + ltrim(str(cnt))
from cte

To get your count. Hopefully you have a primary key that you could join it back and do the update.

Simi

 

[sabinUE]

- I added a field ordNo, for display reason , because row_number can reorder it. you can discard it if the order is not important for you

with tblCountry
AS( select 1 as ordNo,'Spain' as country union all
	select 2,'Greece' union all
	select 3,'UK' union all
	select 4,'Spain' union all
	select 5,'Japan' union all
	select 6,'Australia' union all
	select 7,'Japan' union all
	select 8,'Norway' union all
	select 9,'US' union all
	select 10,'France' union all
	select 11,'US' union all
	select 12,'Spain')

select
A.country + case when A.countNo>1 then cast(A.rw as varchar(30)) else '' end as [country]
from 
(
select 
	A.country 
	,ROW_NUMBER() OVER(PARTITION BY A.country ORDER BY A.country) as rw 
	,B.countNo
	,A.ordNo
from tblCountry A
inner join
(select country,count(*) as countNo from tblCountry group by country) as B on B.country=A.country

)A
order by ordNo

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin

 

[damipera]

sabinUE and simian336 Thank you for the ideas. That seem to work!