Query to fetch Single column data values into 8 defined number of columns

Question

I have a table with a single column,

Look like:

    NAMEs
-------------------
    abc
    bcd
    cde
    def
    efg
    fgh
    ghi
    hij
    ijk
    jkl
    klm

I need help to fetch those items in a matrix format with fixed 8 columns

Expected O/P

[1] |   [2] |   [3] |   [4] |   [5] |   [6] |   [7] |   [8] |
----|-------|-------|-------|-------|-------|-------|-------|
abc |   bcd |   cde |   def |   efg |   fgh |   ghi |   hij |
ijk |   jkl |   klm |   ... |   ... |   ... |   ... |   ... |
... |   ... |   ... |   ... |   ... |   ... |   ... |   ... |

Basically, I want to use this format to be display in SSRS report, So if there is any other way to directly get the same output in SSRS will also be appreciated.

Answer 1

Using a division and modulo operator and by pivoting it, you can do it like,

select [1],[2],[3],[4],[5],[6],[7],[8]
FROM  
(select names, 
1 + ((row_number() over (order by names) - 1) / 8) as rn1,  -- to mark the iteration
1 + ((row_number() over (order by names) - 1) % 8) as rn2 -- to mark the column to be pivoted
from test) tbl
pivot
(
  max(names)
  for rn2 in ([1],[2],[3],[4],[5],[6],[7],[8])
) piv;

fiddle