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;

Answer 3

Assuming your table does not simply holds consecutive numbers from 1 to n (if it does, Gordon's answer is better), here is one way to do it:

First, create and populate sample table (Please save is this step in your future questions)

SELECT TOP 100 IDENTITY(int,1,1) AS col1
    INTO table_name
    FROM sys.objects s1       
    CROSS JOIN sys.objects s2 


DELETE
FROM table_name
WHERE col1 IN
(
    SELECT TOP 67 col1
    FROM table_name
    ORDER BY NEWID()
)

(table_name now contains 33 random numbers between 1 and 100)

Using a couple of common table expressions to get column number and row number:

;With Cols as
(
    SELECT  col1, 
            ROW_NUMBER() OVER(ORDER BY col1) % 5 ColNumber
    FROM table_name
), RowsAndCols as
(
    SELECT col1, ColNumber, ROW_NUMBER() OVER(PARTITION BY ColNumber ORDER BY col1) As RowNumber
    FROM Cols
)

And the query:

SELECT c1.col1, c2.col1 As col2, c3.col1 As col3, c4.col1 As col4, c5.col1 As col5
FROM RowsAndCols c1
LEFT JOIN RowsAndCols c2 ON c1.RowNumber = c2.RowNumber AND c2.ColNumber = 2
LEFT JOIN RowsAndCols c3 ON c1.RowNumber = c3.RowNumber AND c3.ColNumber = 3
LEFT JOIN RowsAndCols c4 ON c1.RowNumber = c4.RowNumber AND c4.ColNumber = 4
LEFT JOIN RowsAndCols c5 ON c1.RowNumber = c5.RowNumber AND c5.ColNumber = 0
WHERE c1.ColNumber = 1

Note I've used left joins, so in case the number of rows is not a multiple of five you'll get nulls in the the last columns in the last row.

See a live demo on rexteser.

Answer 4

If you want that output, you don't even need to start with a table:

with cte as (
      select 1 as col1, 2 as col2, 3 as col3, 4 as col4, 5 as col5
      union all
      select 5 + col1, 5 + col2, 5 + col3, 5 + col4, 5 + col5
      from cte
      where 5 + col1 <= 15
     )
select *
from cte;