Divide the rows of a column in equals batches in grid format

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s