I published an article Divide the rows in equals batches few days ago. One of my reader requested me to help him with a requirement to divide a column’s data in equal groups and batches in grid format.
I came up with a script which accepts the dynamic group size and divides the column data across the group numbers in equal batches. You just need to specify the group size. Batch size is automatically derived with the help of total rows in the column and the group size.
/* Sample table and data */
/*
CREATE TABLE tmp_Order (OrderNo INT)
INSERT INTO tmp_Order (OrderNo) VALUES (12000),(12001),(12002),(12003),(12004),(12005),(12006),(12007),(12008),(12009),(12010),(12011)
*/
/* Supply the dynamic number of groups here.
Play with the different group sizes, but it should be 1 or more.
*/
DECLARE @Groups INT = 10
IF ISNULL(@Groups, 0) < 1
BEGIN
RAISERROR ( '@Groups should be 1 or more', 16, 1) ;
RETURN;
END
IF OBJECT_ID('tempdb..#tmp_stag') IS NOT NULL DROP TABLE #tmp_stag;
IF OBJECT_ID('tempdb..##tmp_output') IS NOT NULL EXECUTE ('DROP TABLE ##tmp_output');
DECLARE @TableRowCount INT
, @BatchSize INT
SELECT @TableRowCount = COUNT(1) FROM oehdrhst_sql
SET @BatchSize = CEILING(@TableRowCount / (@Groups * 1.00))
; WITH cte_raw
AS
(
SELECT NTILE(@Groups) OVER(ORDER BY OrderNo) AS DisplayColumn
, OrderNo
FROM tmp_Order
)
, cte_stag
AS
(
SELECT DisplayColumn
, NTILE(@BatchSize) OVER(PARTITION BY DisplayColumn ORDER BY OrderNo) AS DisplayRows
, OrderNo
FROM cte_raw
)
SELECT * INTO #tmp_stag FROM cte_stag
DECLARE @ColumnID INT = 1
, @Hash_Table_Create_SQL VARCHAR(MAX)
, @Load_Data_SQL VARCHAR(MAX)
, @Column_List VARCHAR(MAX)
WHILE (@ColumnID <= @Groups)
BEGIN
IF (@Groups > 1)
BEGIN
IF @ColumnID = 1
BEGIN
SET @Hash_Table_Create_SQL = 'CREATE TABLE ##tmp_output' + + CHAR(13) + CHAR(10) + CHAR(9) + '(' + QUOTENAME(CAST(@ColumnID AS VARCHAR)) + ' INT' + CHAR(13) + CHAR(10) + CHAR(9);
SET @Column_List = QUOTENAME(CAST(@ColumnID AS VARCHAR));
END
ELSE IF @ColumnID = @Groups
BEGIN
SET @Hash_Table_Create_SQL = @Hash_Table_Create_SQL + ', ' + QUOTENAME(CAST(@ColumnID AS VARCHAR)) + ' INT' + ')';
SET @Column_List = @Column_List + ', ' + QUOTENAME(CAST(@ColumnID AS VARCHAR));
END
ELSE
BEGIN
SET @Hash_Table_Create_SQL = @Hash_Table_Create_SQL + ', ' + QUOTENAME(CAST(@ColumnID AS VARCHAR)) + ' INT' + CHAR(13) + CHAR(10) + CHAR(9);
SET @Column_List = @Column_List + ', ' + QUOTENAME(CAST(@ColumnID AS VARCHAR));
END
END
ELSE
BEGIN
SET @Hash_Table_Create_SQL = 'CREATE TABLE ##tmp_output' + + CHAR(13) + CHAR(10) + CHAR(9) + '(' + QUOTENAME(CAST(@ColumnID AS VARCHAR)) + ' INT' + ')';
SET @Column_List = QUOTENAME(CAST(@ColumnID AS VARCHAR));
END
SET @ColumnID = @ColumnID + 1;
END
EXECUTE (@Hash_Table_Create_SQL);
DECLARE @RowID INT = 1
WHILE (@RowID <= @BatchSize)
BEGIN
SET @Load_Data_SQL = 'INSERT INTO ##tmp_output (' + @Column_List + ')' + CHAR(13) + CHAR(10) +
'SELECT ' + @Column_List + CHAR(13) + CHAR(10) +
'FROM #tmp_stag
PIVOT
(
MAX(OrderNo)
FOR [DisplayColumn] IN ( ' + @Column_List + ')
) AS PVT
WHERE DisplayRows = ' + CAST(@RowID AS VARCHAR)
PRINT @Load_Data_SQL;
EXECUTE (@Load_Data_SQL);
SET @RowID = @RowID + 1;
END
EXECUTE ('SELECT * FROM ##tmp_output');
Output of the script will look like similar to as can be seen in the image below. Total rows in the column is 12. Group size is 10, and the batch size is 2.
If you’ll change the group size to a lesser number such as 5 then the output will look like similar to as can be seen in the image below. Total rows in the column is 12. Group size is 5, and the batch size is 3.
If you’ll change the group size to 4 then the output will look like similar to as can be seen in the image below. Total rows in the column is 12. Group size is 4, and the batch size is 3.
