CODEWITHSUNDEEP
sqlsql-servert-sql
Lynchie
Lynchie

Reputation: 1149

Split SQL rows between 4 flags

How can I divide every 4 rows of data between different users.

i.e.

Number

1
2
3
4
5
6
7
8

Then run a query so that I get the following format:

Number | User 
1      | joe
2      | jim
3      | gill
4      | jack
5      | joe
6      | jim
7      | gill
8      | jack

etc.

UPDATE

My Code ended up being (T-SQL / SQL 2008)

SELECT CASE 
    WHEN ROW_NUMBER() OVER(ORDER BY number) % 4 = 1 THEN 'joe'
    WHEN ROW_NUMBER() OVER(ORDER BY number) % 4 = 2 THEN 'jim'
    WHEN ROW_NUMBER() OVER(ORDER BY number) % 4 = 3 THEN 'gill'
    WHEN ROW_NUMBER() OVER(ORDER BY number) % 4 = 0 THEN 'jack'
END

Upvotes: 1

Views: 475

Answers (2)

Paul
Paul

Reputation: 1066

When number has some gaps:

SELECT CASE 
        WHEN MOD(ROW_NUMBER() OVER(ORDER BY number), 4) = 1 THEN 'joe'
        WHEN MOD(ROW_NUMBER() OVER(ORDER BY number), 4) = 2 THEN 'jim'
        WHEN MOD(ROW_NUMBER() OVER(ORDER BY number), 4) = 3 THEN 'gill'
        WHEN MOD(ROW_NUMBER() OVER(ORDER BY number), 4) = 0 THEN 'jack'
    END
FROM t

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269963

Just use the modulus operator in a case statement:

select number,
       (case when mod(number, 4) = 1 then 'joe'
             when mod(number, 4) = 2 then 'jim'
             when mod(number, 4) = 3 then 'gill'
             when mod(number, 4) = 0 then 'jack'
        end)
from t;

Upvotes: 6

Related Questions