help in creating query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

help in creating query

Author

Topic

shyaagi
Starting Member

1 Post

Posted - 2010-11-22 : 12:58:55

Hi my data is

Id amount
213 500
214 600
215 700
215 400
215 550
216 300
216 600
217 500

I am looking for output like below

Id amount
213 500
214 600
21501 700
21502 400
21503 550
21601 300
21602 600
217 500

if same id repetes then it has to add 01,02,03 after the id

is it possible

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-22 : 13:08:18

with cte as
(select id, amount, seq = row_number() over (partition by id order by amount) from tbl)
select id, seq, amount from cte

or maybe
with cte as
(select id, amount, seq = row_number() over (partition by id order by amount) from tbl)
select c1.id + case when c2.seq <> 1 then right('00' + convert(varchar(2),c1.seq),2) else '' end, amount
from cte c1
join (select id, seq = max(seq) from cte group by id) c2
on c1.id = c2.id

Hopefully Mr. Celko is still around to give a pertinent comment about this.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources