How to assign an alternating row number based on a table primary key?

I have a table with data named Product

ProductID  ProductName
1            ABC
2            PQR
3            XYZ
4            HJK
5            LKJ
6            MNB
...          .... 

with many more product in it. What I want is result like this on Select query:

RowNo ProductID ProductName
1      1           ABC
1      2           PQR
2      3           XYZ
2      4           HJK
1      5           LKJ
1      6           MNB
2      7           klj
2      8           hjg

then 1,1, 2,2 1,1 for the number of records in the table. Is it possible, and if so how can I do th开发者_如何转开发at?

This works for your sample data which assumes ProductID is contiguous:

SELECT
   CASE WHEN ProductID % 4 = 0 OR (ProductID+1) % 4 = 0 THEN 2 ELSE 1 END,
   ProductID,
   ProductName
FROM
   Product

Now, guessing that you mean in resultset which may have gaps in ProductID

SELECT
   CASE WHEN ContiguousProductID % 4 = 0 OR (ContiguousProductID+1) % 4 = 0 THEN 2 ELSE 1 END,
   --ContiguousProductID,
   --CASE WHEN ProductID % 4 = 0 OR (ProductID+1) % 4 = 0 THEN 2 ELSE 1 END,
   ProductID,
   ProductName
FROM
    (
    SELECT
        ROW_NUMBER() OVER (ORDER BY ProductID) AS ContiguousProductID,
        ProductName, ProductID
    FROM 
        dbo.Product
    ) P2