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

Tags: ,



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

And what I would like it to be able to bring in, is the above, but split into 4 (or X) equal parts.

Column1Number Column2Text
001 Desc1
25 Desc25
Column1Number Column2Text
26 Desc26
50 Desc50
Column1Number Column2Text
51 Desc51
75 Desc75
Column1Number Column2Text
76 Desc76
100 Desc100

The above 4 tables could then be displayed next to each other horizontally.

There will be “other” filters on the WHERE clause but it will result in a list of numbers and their corresponding text. Think product number and product description for an example.

What I think I need is a method of dividing the MAX(Column1Number) by 4 and putting that result into a variable. Perhaps something along these lines.

DECLARE @MinNumber VARCHAR(100) = MIN(Column1Number)
DECLARE @MaxNumber VARCHAR(100) = MAX(Column1Number)
DECLARE @QuarterMaxNumber VARCHAR(100) = @MaxNumber/4

From there, I should be able to “quarter” the variables

DECLARE @FirstQuarter VARCHAR(100) = @MinNumber + @QuarterMaxNumber
DECLARE @ThirdQuarter VARCHAR(100) = @MaxNumber - @QuarterMaxNumber
DECLARE @MidWayPoint VARCHAR(100) = @MaxNumber/2

Finally, some relatively simple adjustments to the 4 SQL queries.

SELECT Column1Number
      ,Column2Text
FROM TableExample
WHERE Column1Number >= @MinNumber AND Column1Number < @FirstQuarter
SELECT Column1Number
      ,Column2Text
FROM TableExample
WHERE Column1Number >= @FirstQuarter AND Column1Number < @MidWayPoint
SELECT Column1Number
      ,Column2Text
FROM TableExample
WHERE Column1Number >= @MidWayPoint AND Column1Number < @ThirdQuarter
SELECT Column1Number
      ,Column2Text
FROM TableExample
WHERE Column1Number >= @ThirdQuarter AND Column1Number <= @MaxNumber

I think the above will work with contiguous numbers. i.e. a continuous count of 1 to 100 but will break if some numbers are missing, or the list doesn’t start at 1. By break, I mean the 4 queries won’t be equal in length.

I will be able to have the same set of variables in 4 separate queries, and custom setting the WHERE clause is also not an issue. It probably won’t be efficient, but that isn’t much of a concern at the moment.

In the end. I’m hoping there is some trick to this.

All help is appreciated.

Answer

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.



Source: stackoverflow