sql - Need help grouping a column that may or may not have the same value but have the same accounts -

how output data stored in table 1 each account number has has same cpt group's ones not match fall bottom of list?

i have 1 table: select * cptcounts , displays

format (relevant fields only):

account    originalcpt       count          modifiedcpt  count 11                           0              71010        1 11         71010             1                           0 2                            0              71010        1 2                            0              71020        9 2                            0              73130        1 2                            0              77800        1   2          71010             1                           0 2          71020             8                           0 2          73130             1                           0 2          73610             1                           0 2          g0202             4                           0 31         99010             1                           0 31                           0              99010        4 31                           0              99700        2 

what want results grouped below... , display or similar.

account     originalcpt    count      modifiedcpt   count 11          71010           1          71010         1 2           71010           1          71010         1 2           71020           8                        0 2           73130           1                        0 2           73610           1                        0 2           g0202           4                        0 31          99010           1          99010         4 31                          0          99700         2 

i have 1 table values above;

select * #cptcounts

the grouping looking original = modified cpt , not have value in 1 side or other of times have match. place of unmatched ones @ bottom of account.

any suggestions?

i thinking of creating second table , joining 2 account how return each value?

select cpt1.account, cpt1.originalcpt, cpt1.count, cpt2.modifiedcpt, cpt2.count
#cptcounts cpt1
join #cptcounts cpt2 on cpt1.accont = cpt2.account

but having trouble solution.

i'm not sure have exact solution, perhaps food thought @ least. fact need either "original" or "modified" set of columns makes me think need full outer join rather left join. don't mention database using. in mysql, example, full joins can emulated means of union of left , right join, in following:

select cpt1.account, cpt1.originalcpt, cpt1.counto, cpt2.modifiedcpt, cpt2.countm cptcounts cpt1 left outer join cptcounts cpt2    on cpt1.account = cpt2.account    , cpt1.originalcpt=cpt2.modifiedcpt cpt1.account not null    , (cpt1.originalcpt not null or cpt2.modifiedcpt not null) union select cpt1.account, cpt1.originalcpt, cpt1.counto, cpt2.modifiedcpt, cpt2.countm cptcounts cpt1 right outer join cptcounts cpt2    on cpt1.account = cpt2.account    , cpt1.originalcpt=cpt2.modifiedcpt cpt2.account not null    , (cpt1.originalcpt not null or cpt2.modifiedcpt not null) order originalcpt, modifiedcpt, account 

the ordering brings non-matching rows top, seemed lesser problem getting matching work.

(your output data bit confusing, because cpt 71020, example, occurs in both original , modified columns, haven't shown 1 of matching ones in result set. i'm presuming because example... if i'm wrong, missing part of intention.)

you can play around in sql fiddle.