SQL Syntax

Hope this helps:

SELECT CASE WHEN isnumeric(PatAccount) = 1 THEN cast(PatAccount AS int)
ELSE cast(isnull(rtrim(PatAccount),0) AS int) AS PatAccount_without_zero,
CASE WHEN isnumeric(PatAccount) = 1 THEN '0' + cast(PatAccount AS varchar(10))
ELSE '0' + cast(isnull(rtrim(PatAccount),0) AS int) AS PatAccount_with_zero

I'm not quite sure of your table structures but give this a try.  It wasn't clear if 56789 and 056789 are on different rows in the table?
Also, do both of these PatAccounts have different AcctID's but you are only showing the first one?  Or can AcctID be used to join them together.  See if this gives you an idea of what you need to do.

SELECT MyTable.AcctID , MyTable.PatAccount, MyTable.Status, MyTable_2.sPatAccount, MyTable_2.Status
FROM MyTable
LEFT OUTER JOIN
(SELECT PatAccount AS sPatAccount, convert(int,PatAccount) as iPatAccount, Status from MyTable WHERE Left(PatAccount,1) = '0') as MyTable_2
On MyTable.PatAccount = MyTable_2.iPatAccount

Select allOpen in new window

Actually there are Pataccounts that the same numbers but one has a leading zero. They both have there own record. They both already exist. I needed to update my recordset because they both have different AcctIDs. Am I making sense?

AcctID      PatAccount   Status      AcctID       PatAccount   Status
123             56789           CW           789          056789          UP
456             89768           PO            987          089768          NC

Then change my query as follows:
(Please note this is untested)

SELECT MyTable.AcctID , MyTable.PatAccount, MyTable.Status, MyTable_2.sAcctID, MyTable_2.sPatAccount, MyTable_2.sStatus
FROM MyTable
LEFT OUTER JOIN
(SELECT MyTable_2.AcctID AS sAcctID, PatAccount AS sPatAccount, convert(int,PatAccount) as iPatAccount, Status As sStatus from MyTable WHERE Left(PatAccount,1) = '0') as MyTable_2
On MyTable.PatAccount = MyTable_2.iPatAccount

Select allOpen in new window

Im sorry dwe, Im trying to convert everything to my table name and it doesnt seem to be working. The table is accounts_master....Could you give me a hand again...Thanks so much

Have you tried using my query, Including the table names here and if your prior result set query was:

SELECT AcctID, PatAccount, Status, AcctID, PatAccount, Status
FROM accounts_master

Then your query would look like:

Hope this helps

SELECT AcctID, CASE WHEN isnumeric(PatAccount) = 1 THEN '0' + cast(PatAccount AS varchar(10))
ELSE '0' + cast(isnull(rtrim(PatAccount),0) AS int) AS PatAccount_with_zero, Status as Status1, AcctID, CASE WHEN isnumeric(PatAccount) = 1 THEN cast(PatAccount AS int)
ELSE cast(isnull(rtrim(PatAccount),0) AS int) AS PatAccount_without_zero, Status as Status2
FROM accounts_master

Select allOpen in new window

This will probably do it for you.

SELECT T1.AcctID , 
       T1.PatAccount, 
       T1.Status, 
       T2.sAcctID, 
       T2.sPatAccount, 
       T2.sStatus
FROM accounts_master AS T1
LEFT OUTER JOIN
(SELECT AcctID AS sAcctID, 
        PatAccount AS sPatAccount, 
        CONVERT( VarChar(10), convert(int,PatAccount)) AS iPatAccount, 
        Status As sStatus 
 FROM   accounts_master 
 WHERE Left(PatAccount,1) = '0'
) AS T2
ON T1.PatAccount = T2.iPatAccount
WHERE T2.iPatAccount IS NOT NULL;

Select allOpen in new window

Try something like this.  By the way, what error are you getting?

Re: 8080_Diver's suggestion, I don't think you want the last WHERE clause because I'm assuming you want to pull in rows from T1 even when there is not a corresponding PatAccount with leading 0.

SELECT AM.AcctID , AM.PatAccount, AM.Status, 
			AM_2.sAcctID, AM_2.sPatAccount, AM_2.sStatus
FROM accounts_master AM
LEFT OUTER JOIN
(SELECT 
	AcctID AS sAcctID, 
	PatAccount AS sPatAccount, 
	convert(int,PatAccount) as iPatAccount, 
	Status As sStatus 
from accounts_master WHERE Left(PatAccount,1) = '0') as AM_2
On AM.PatAccount = AM_2.iPatAccount

Select allOpen in new window

@dwe761,
Actually, the initial question seems to imply a desire to see only the "duplicate" rows.
Aslo, the ON clause in your query is forcing an implicit conversion of either the PatAccount or the iPatAccount, which may well result in performance degradation.  
@healthcheckinc,
I guess you have to be the final arbiter of the question as to whether you want only the "duplicates" (i.e. include the final WHERE clause) or not (i.e. omit the final WHERE clause). ;-)

All,

I see that there are alot of Converts going on and its possible from my one post you thought thats what I wanted to do. Simplified....I have 2(well alot more than 2 but...) records in the same table that I need to identify. One will look like this 0123456789 and the other will look like this 123456789 in seperate records. I want to bring in the acctID, patient_account and the status for each of them. No converting, no concatenating just a visual of the 2 rows values into 1 row....I also have to have Where hospID = 1...Does this make sense? Sorry for any confusion

acctID    PatAccount     Status    acctID         PatAccount      Status
123       0123456789      CW         987            123456789        UP

One more thing, they are not exact duplicates. 1 has a leading zero and the other does not...

Yes, we've already given you a queries to accomplish what you're after.  Perhaps what is unclear to you is that the convert is necessary because we are changing a string to numeric in order to be able to join the table to the subquery together on a field of a common type. So, 8080_Diver's point is that to get better performance, you should convert the field to join so the server does not have to figure that out.

The question we posed to you is do you ONLY want to return rows where there are both 123456789 and 0123456789 pairs or do you want to return rows sometimes when there is not both pairs but only one of them.  If you ONLY want to return rows that have both with and without a leading zero, then include the WHERE clause:
WHERE AM_2.iPatAccount IS NOT NULL;

SELECT AM.AcctID , AM.PatAccount, AM.Status, 
                        AM_2.sAcctID, AM_2.sPatAccount, AM_2.sStatus
FROM accounts_master AM
LEFT OUTER JOIN
(SELECT 
        AcctID AS sAcctID, 
        PatAccount AS sPatAccount, 
        convert(varchar(10),convert(int,PatAccount)) as iPatAccount, 
        Status As sStatus 
from accounts_master WHERE Left(PatAccount,1) = '0') as AM_2
On AM.PatAccount = AM_2.iPatAccount

Select allOpen in new window

@healthcheckinc,
Any actions done in a SELECT statement do not have any impact on any data in the database.  THose actions only impact what is going on in the SELECT statement.
In other words, the convert statements are not in any way modifying any data in the database but, rather, are only modifying how the data may appear in the results of the SELECT statement (assuming that they modified values appear in the final set of selected columns).
Please, note, I am trying very hard not to raise questions about the fact that the PatAccount is a character column (which has contributed to this problem) and is apparently being allowed to be entered by either more than one person or more than one process and without any consistent controls on what is being entered.

8080 Diver: I understand the concept of a Select, Im trying to just let everyone know that no Convert or concat is neccesary. I did not build the database, although I did build a program that filters out any possible duplicate so going forward this wont happen again. I am in the process of cleaning up old data.
This worked to build a table of dups

select am.account_id,am.patient_account as 'Dup',am.audit_status as 'DupStatus',
am1.GAccountID,am1.GPatAccount,am1.PatAcctIDStatus
into DupsTest
from accounts_master am
inner join
(select account_id as 'GAccountID',patient_account as 'GPatAccount',
right(patient_account,9)as 'PatAcctID',audit_status as 'PatAcctIDStatus'
from accounts_master where len(patient_account)>9 and Hospital_id = 1

healthcheckinc,
Im trying to just let everyone know that no Convert or concat is neccesary.
 No offense taken by your statement and no offense intended by the following.
How is it that you know that no convert is required given that you have asked for our help?  Are you now stating a new constraint on the nature of the solution you are asking for?  
What I am saying is that the CONVERTS that I used in the query I provided are for expediency and do not have anything to do with what is displayed (other than making it possible to select the desired results).  If the solution provided works, whether it has concatenate, convert, cast, or even reverse (and, yes, I can think of a solution that uses REVERSE ;-), does anything matter except the fact that it works and it does so with reasonable speed? ;-)
Now, if you must have tis solution sans converts and casts, then are you willing to allow the use of LEFT and RIGHT functions? ;-)

Thanks