how to split single integer value columns into n column?

Question

I have a SQL Server table table_name like:

col1   
1
2
3
4
5
6
7
8
9
.
.
.
N

i want output like below

col-1 | col-2 | col-3 | col-4 |col-5
-------------------------------
1     | 2     | 3     | 4     |5
6     | 7     | 8     | 9     |10
11    | 12    | 13    | 14    |15

how to get from this output in sql-server query.

Answer 1

Another option is a Dynamic Pivot.

This will create N columns.

Example

Declare @nCols int  = 5

Declare @SQL varchar(max) = '
Declare @nCols int = '+str(@nCols,5)+'
Select *
 From (
        Select RowNr = ((row_number() over (order by col1)-1)/@nCols)+1
              ,ColNr = concat(''col-'',isnull(nullif((row_number() over (order by col1))%@nCols,0),@nCols))
              ,Value = col1
         From YourTable
      ) A
 Pivot (max(Value) For ColNr in (' + Stuff((Select Top (@nCols) ','+QuoteName(concat('col-',Row_Number() Over (Order By (Select NULL)))) From  YourTable For XML Path('')) ,1,1,'') + ') ) p'
Exec(@SQL);
--Print @SQL

Returns

If @nCols = 3 , the Results are:

EDIT a Fully Parameter Driven Version

This version you can supply the

1. Number of Columns @NbrCols (same as above)
2. Source @FromSrc, which is the table name or a SQL String in () i.e. (Select ...)
3. The column name @ColName to Pivot @ColName
4. The colum prefix @ColPrfx i.e. 'Col-' or even ''

---

Declare @NbrCols int  = 5
Declare @FromSrc varchar(max) = 'YourTable'  -- Or SQL '(Select col ...)'
Declare @ColName varchar(100) = 'col1'
Declare @ColPrfx varchar(100) = 'Col-'

Declare @SQL varchar(max) = '
Declare @NbrCols int = '+str(@NbrCols,5)+'
Select *
 From (
        Select Row = ((row_number() over (order by '+quotename(@ColName)+')-1)/@NbrCols)+1
              ,Col = concat('''+@ColPrfx+''',isnull(nullif((row_number() over (order by '+quotename(@ColName)+'))%@NbrCols,0),@NbrCols))
              ,Val = '+quotename(@ColName)+'
         From '+@FromSrc+' A1
      ) A
 Pivot (max(Val) For Col in (' + Stuff((Select Top (@NbrCols) ','+QuoteName(concat(@ColPrfx,Row_Number() Over (Order By (Select NULL)))) From master..spt_values For XML Path('')) ,1,1,'') + ') ) p'
Exec(@SQL);
--Print @SQL

Answer 2

Using conditional aggregation based on dividing a row_number(), and using modulo % for column placement:

test setup:

select n into dbo.numbers from (values 
  (1), (2), (3), (4), (5), (6), (7), (8), (9),(10)
,(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)
) t(n)
delete from dbo.numbers where n in (12,13,17);

query:

select 
    col1 = sum(case when rn%5=0 then n end)
  , col2 = sum(case when rn%5=1 then n end)
  , col3 = sum(case when rn%5=2 then n end)
  , col4 = sum(case when rn%5=3 then n end)
  , col5 = sum(case when rn%5=4 then n end)
from (
  select n, rn = row_number() over (order by n)-1
  from dbo.numbers
  ) t
group by rn/5;

rextester demo: http://rextester.com/UHKY16981

returns:

+------+------+------+------+------+
| col1 | col2 | col3 | col4 | col5 |
+------+------+------+------+------+
|    1 |    2 | 3    | 4    | 5    |
|    6 |    7 | 8    | 9    | 10   |
|   11 |   14 | 15   | 16   | 18   |
|   19 |   20 | NULL | NULL | NULL |
+------+------+------+------+------+

---

The same concept but using pivot() instead of conditional aggregation returns the same results.

select 
    col1 = [0]
  , col2 = [1]
  , col3 = [2]
  , col4 = [3]
  , col5 = [4]
from (
  select n
    , rn  = (row_number() over (order by n)-1)%5
    , grp = (row_number() over (order by n)-1)/5
  from dbo.numbers
  ) t
pivot (sum(n) for rn in ([0],[1],[2],[3],[4])) p;