Number acording to subresults - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Number acording to subresults

Author

Topic

jocast
Starting Member

8 Posts

Posted - 2012-01-17 : 11:53:03

Hello, I am having some problems reaching a result, let me explain. I have a table with several columns the ones that matter here is
pn and po

PO PN
abc123 erte56546
abc123 ertbd4564
abc123 sdbdfb245
bdg214 sdgsdf548
bdg214 sdfsd1651

and i need this
PO PN newpo
abc123 erte56546 abc1230001
abc123 ertbd4564 abc1230002
abc123 sdbdfb245 abc1230003
bdg214 sdgsdf548 bdg2140001
bdg214 sdfsd1651 bdg2140002

I need to include a consecutive number to the po for each pn
right now i only get the amount of PN per PO using the query below.

select PO, max(PN) , count(PO) as qty from mytable
group by PO

Please help.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-17 : 12:18:38

Why do you want to do this?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

jocast
Starting Member

8 Posts

Posted - 2012-01-17 : 12:30:16

That's the way a program is developed. And it works with a table I currently import , but I need to importa new table that does not have this suffix so i have to includeit to the newpo

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-17 : 15:54:34

Hello jocast,

Perhaps the ROW_NUMBER function would help here? something like the following;


SELECT PO, PN, PO + RIGHT('0000' + CAST(ROW_NUMBER() OVER (ORDER BY PO, PN) AS VARCHAR(4)),4) AS 'newpo' 
FROM @Table
ORDER BY PO, PN --or whatever you need to order by

Hope that helps.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources