tsql - Is there a way of getting a proportional section of an SQL table?

Question

I'm trying to split a single query into 4 parts, for readability purposes.

Having it as a single query, while adequate, isn't "pretty" for display purposes.

An example would be a query that brings in something akin to the below.

SELECT Column1Number
      ,Column2Text
FROM TableExample
WHERE Column1Number BETWEEN 1 AND 100

Column1Number	Column2Text
001	Desc1
...	...
100	Desc100

question from:https://stackoverflow.com/questions/65851644/is-there-a-way-of-getting-a-proportional-section-of-an-sql-table

Answer 1

For this you could use NTILE. NTILE is T-SQL's way of dividing rows as evenly as possible.

--==== 1. Sample Data
DECLARE @table TABLE (SomeNbr INT IDENTITY, SomeValue VARCHAR(50));
INSERT @table(SomeValue) SELECT TOP (11) NEWID() FROM sys.all_columns;

--==== Solution
SELECT 
  TileGroup = NTILE(4) OVER (ORDER BY t.SomeNbr), t.SomeNbr, t.SomeValue 
FROM   @table AS t;

Returns:

TileGroup   SomeNbr  SomeValue
----------- -------- ---------------
1           1        C3DB121B-C353-4...
1           2        A6C40211-D53B-4...
1           3        A2089286-1106-4...
2           4        3392E634-1D5E-4...
2           5        9006F0EB-1FDB-4...
2           6        831175BF-2783-4...
3           7        339216A6-AB6C-4...
3           8        29EAEECA-27E9-4...
3           9        CA302532-40F1-4...
4           10       29A41096-A786-4...
4           11       E0CA6CD4-FCB4-4...

Note that the performance of NTILE is not spectacular. For better performance you can use an NTally Table as this is the fastest method for evenly dividing rows; far more performance than NTILE. There's a little more work required though.