WITH    testdata
          AS ( SELECT   222 AS ID ,
                        'CA' AS SOP
               UNION ALL
               SELECT   223 AS ID ,
                        'IL' AS SOP
               UNION ALL
               SELECT   224 AS ID ,
                        'VA' AS SOP
               UNION ALL
               SELECT   225 AS ID ,
                        'DC' AS SOP
               UNION ALL
               SELECT   226 AS ID ,
                        'CA' AS SOP
               UNION ALL
               SELECT   226 AS ID ,
                        'CO' AS SOP
             )
    SELECT   ID ,
            SOP,row_number() OVER(PARTITION BY id ORDER BY id) AS ElementCount
    FROM    testdata