SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select *
into t
from (values
     (1, 'N'),
     (2, 'C'),
     (3, 'C'),
     (4, 'P'),
     (5, 'N'),
     (6, 'N'),
     (7, 'C'),
     (8, 'N'),
     (9, 'P')
    ) v(idx, value)
9 rows affected

select max(case when (idx - 1) / num_rows = 0 then value end) as grp_1,
       max(case when (idx - 1) / num_rows = 1 then value end) as grp_2,
       max(case when (idx - 1) / num_rows = 2 then value end) as grp_3
from (select t.*, ceiling(count(*) over () / 3) as num_rows
      from t
     ) t
group by idx % num_rows
order by min(idx)


grp_1
grp_2
grp_3


N
P
C

C
N
N

C
N
P


Warning: Null value is eliminated by an aggregate or other SET operation.


select max(case when (idx - 1) / num_rows = 0 then idx end) as grp_1,
       max(case when (idx - 1) / num_rows = 1 then idx end) as grp_2,
       max(case when (idx - 1) / num_rows = 2 then idx end) as grp_3
from (select t.*, convert(int, ceiling(count(*) over () / 3.0)) as num_rows
      from t
     ) t
group by idx % num_rows
order by min(idx)


grp_1
grp_2
grp_3


1
4
7

2
5
8

3
6
9


Warning: Null value is eliminated by an aggregate or other SET operation.


insert into t values (10, 'X') --, (11, 'Y'), (12, 'Z')
1 rows affected

select max(case when (idx - 1) / num_rows = 0 then idx end) as grp_1,
       max(case when (idx - 1) / num_rows = 1 then idx end) as grp_2,
       max(case when (idx - 1) / num_rows = 2 then idx end) as grp_3
from (select t.*, convert(int, ceiling(count(*) over () / 3.0)) as num_rows
      from t
     ) t
group by idx % num_rows
order by min(idx)


grp_1
grp_2
grp_3


1
5
9

2
6
10

3
7
null

4
8
null


Warning: Null value is eliminated by an aggregate or other SET operation.