How to split columns of integer value into n columns?
Another option is dynamic vault.
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:
CHANGE full parameter version
In this version, you can specify
- Number of columns
@NbrCols
(as above) - Source
@FromSrc
which is table name or SQL string in () ie(Select ...)
- Column name
@ColName
in Pivot@ColName
- Colum
@ColPrfx
ie prefix'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
source to share
Using conditional aggregation based on division and using modulo to arrange columns: row_number()
%
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);
request:
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;
demo version of rexter: 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 |
+------+------+------+------+------+
Same concept, but using pivot()
conditional aggregation instead, 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;
source to share
Assuming your table doesn't just contain sequential numbers from 1 to n (if so, then Gordon's answer is better), here's one way to do it:
First create and fill in the example table ( Please keep 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 from 1 to 100)
Using a few common table expressions to get the 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 request:
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 used left joins, so if the number of rows is not a multiple of five, you will get zero values in the last columns in the last row.
source to share