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.
Advertisement
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.