Row number - 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

Row number

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-06 : 06:45:50

Hi,

I have the following query which works OK but need to amend it so that the value of pat ID (will be a concatenation of the incremented value + pracID) as shown in the example below...

        
 select *, row_number() over (partition by pracid order by pracid) as PatID
 from dbo.QryPatientExtraction  WITH (tablock) order by pracid, patid

Current Results,

PracID    PatID
20001       1
20001       2
20001       3
20002       1
20002       2
20003       1

I want to formulate the above query to output

PracID      PatID
20001       120001
20001       220001
20001       320001
20002       120002
20002       220002
20003       120003

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-10-06 : 06:57:15

Try this:


select PracID, CAST(PatID as Varchar(10)) + CAST(PracID as varchar(10)) from
(
select PracID, row_number() over (partition by pracid order by pracid) as PatID
 from dbo.QryPatientExtraction  WITH (tablock)
) t
order by PracID, PatID

Harsh Athalye
http://in.linkedin.com/in/harshathalye/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources