Grouping records and starting line numbers as an odd number when changing a section

Question

See below DDL:

create table Test (RevObjId int, LegalPartyId int, IsPrimary int)

insert into Test (RevObjId, LegalPartyId, IsPrimary) values (10, 20, 0)
insert into Test (RevObjId, LegalPartyId, IsPrimary) values (10, 21, 0)
insert into Test (RevObjId, LegalPartyId, IsPrimary) values (10, 22, 1)
insert into Test (RevObjId, LegalPartyId, IsPrimary) values (11, 20, 1)
insert into Test (RevObjId, LegalPartyId, IsPrimary) values (11, 21, 0)
insert into Test (RevObjId, LegalPartyId, IsPrimary) values (12, 30, 1)
insert into Test (RevObjId, LegalPartyId, IsPrimary) values (13, 40, 0)

I am looking for the result below:

RevObjId LegalPartyId IsPrimary RowNumber
10 22 1 1
10 20 0 2
10 21 0 3
11 20 1 5
11 21 0 6
12 30 1 7
13 40 0 9

When I use the following query:

select RevObjId, 
       LegalPartyId, 
       IsPrimary, 
       row_number() over(partition by RevObjId order by RevObjId asc,IsPrimary desc,LegalPartyId asc) as RowNumber 
       from test;

I get the line numbers in the sequence, each line is incremented by one, the line numbers get reset after changing the section. How to change the line number to the next odd number when changing the section (by RevObjId)? Here is my SQL Fiddle http://sqlfiddle.com/#!6/01d5c/22

We need gaps between line numbers because I need to generate a report in the following format.

Here is the support question I asked: How to convert each row to column in T-SQL?

Answer 1

, , .

, , - , 1 . , 1 .

WITH T
     AS (SELECT RevObjId,
                LegalPartyId,
                IsPrimary,
                odd_adj = CASE
                            WHEN RevObjId = LEAD(RevObjId) 
                                    OVER (ORDER BY RevObjId ASC, IsPrimary DESC, LegalPartyId ASC)
                              THEN 0
                            ELSE ROW_NUMBER() /*We are in the last row of the partition so can use rownumber as a more efficient alternative to count*/
                                    OVER (PARTITION BY RevObjId ORDER BY IsPrimary DESC, LegalPartyId ASC)%2
                          END,
                RowNumber = ROW_NUMBER() 
                                OVER(ORDER BY RevObjId ASC, IsPrimary DESC, LegalPartyId ASC)
         FROM   test)
SELECT RevObjId,
       LegalPartyId,
       IsPrimary,
       RowNumber
           +  SUM(odd_adj) OVER (ORDER BY RevObjId 
                                 ROWS UNBOUNDED PRECEDING) 
           - odd_adj AS RowNumber /*odd_adj is only potentially non zero for the last row in each partition 
                                   - if we are in the last row and it is 1 we need to deduct it 
                                     as this is not a previous partition */
FROM   T; 

SQL Fiddle

Answer 2

:

with groupcount as
(
  select RevObjId, (count(*) + 1)/ 2 * 2 as c
  from test
  group by RevObjId
), RevObjIdRN as
(
  select RevObjId, LegalPartyId, IsPrimary, 
         row_number() over (partition by RevObjId order by IsPrimary desc,LegalPartyId asc) as rn
  from test
)
select t2.RevObjId, t2.LegalPartyId, t2.IsPrimary, rn + relative
from (
  select RevObjId, sum(c) over (order by RevObjId) - c as relative
  from groupcount 
) t1  
join RevObjIdRN t2 on t1.RevObjId =  t2.RevObjId

sqlfiddle

:

with groupcount as
(
  select RevObjId, (count(*) + 1)/ 2 * 2 c
  from test
  group by RevObjId
)
select RevObjId, sum(c) over (order by RevObjId) - c as relative
from groupcount

row_number RevObjId ( RevObjId). row_number() RevObjId .