How can I get a sql query that always would be multiple of n?

Question

I need to obtain a query result that would be showing in multiples of a determined number (10 in my case), independent of the real quantity of rows (actually to solve a jasper problem).

For example, in this link, I build a example schema: http://sqlfiddle.com/#!3/c3dba/1/0

I'd like the result to be like this:

1    Item 1     1    10
2    Item 2     2    30
3    Item 3     5    15
4    Item 4     2    10
null null null  null null
null null null  null null
null null null  null null
null null null  null null
null null null  null null
null null null  null null

I have found this explanation, but doesn't work in SQLServer and I can't convert: http://community.jaspersoft.com/questions/514706/need-table-fixed-size-detail-block

why would you want such records? Is it because you are showing your data at a place say gridview with paging 10, or so in order to keep your data display even? because if you are there are other and better ways to do so. — Codeek, Dec 19 '14 at 14:21
Yes, Codeek. I have no found other way to solve this in jasper, so I trying to found the solution obtaing the correct quantity from query. — deldev, Dec 19 '14 at 14:24
Paging example. Accepted answer is for the older version, the other upvoted answer is for SQL 2012: http://stackoverflow.com/questions/187998/row-offset-in-sql-server — KHeaney, Dec 19 '14 at 14:29
there's always a workaround mate. anyways, how will you be selecting the records? will they always be * or based on some condition, so that if the condition is met, you fetch those records and if not fill it with null? — Codeek, Dec 19 '14 at 14:30
There you go. It seems good people have come up with very good solutions :) — Codeek, Dec 19 '14 at 14:35

score 1 · Answer 1 · answered Dec 19 '14 at 14:29

This might work for you - use an arbitrary cross join to create a large number of null rows, and then union them back in with your real data. You'll need to pay extra attention to the ORDERING to ensure that it is the nulls at the bottom.

DECLARE @NumRows INT = 50;

SELECT TOP (@NumRows) *
FROM
(
    SELECT * FROM itens
    UNION
    SELECT TOP (@NumRows) NULL, NULL, NULL, NULL
        FROM sys.objects o1 CROSS JOIN sys.objects o2
) x
ORDER BY CASE WHEN x.ID IS NULL THEN 9999 ELSE ID END

Fiddle Demo

score 1 · Answer 2 · answered Dec 19 '14 at 14:31

1

This is super simple. You use a tally as the main table in your query.

http://sqlfiddle.com/#!3/c3dba/20

You can read more about tally tables here.

http://www.sqlservercentral.com/articles/T-SQL/62867/

answered Dec 19 '14 at 14:31

Sean Lange

33,028
3
25
40

Giorgos Betsos · Accepted Answer · 2014-12-19T17:10:17.757

Another option is to use a recursive CTE to get the pre-determined number of rows, then use a nested CTE construct to union rows from the recursive CTE with the original table and finally use a TOP clause to get the desired number of rows.

DECLARE @n INT = 10

;WITH Nulls AS (
    SELECT 1 AS i
    UNION ALL
    SELECT i + 1 AS i
    FROM Nulls
    WHERE i < @n
),
itemsWithNulls AS
(
   SELECT * FROM itens 
   UNION ALL 
   SELECT NULL, NULL, NULL, NULL FROM Nulls    
)
SELECT TOP (@n) *
FROM itemsWithNulls

EDIT:

By reading the requirements more carefully, the OP actually wants the total number of rows returned to be a multiple of 10. E.g. if table itens has 4 rows then 10 rows should be returned, if itens has 12 rows then 20 rows should be return, etc.

In this case @n should be set to:

DECLARE @n INT = ((SELECT COUNT(*) FROM itens) / 10 + 1) * 10

We can actually fit everything inside a single sql statement with the use of nested CTEs:

;WITH NumberOfRows AS (
   SELECT n = ((SELECT COUNT(*) FROM itens) / 10 + 1) * 10
), Nulls AS (
    SELECT 1 AS i
    UNION ALL
    SELECT i + 1 AS i
    FROM Nulls
    WHERE i < (SELECT n FROM NumberOfRows)
),
itemsWithNulls AS
(
   SELECT * FROM itens 
   UNION ALL 
   SELECT NULL, NULL, NULL, NULL FROM Nulls    
)
SELECT TOP (SELECT n FROM NumberOfRows) *
FROM itemsWithNulls

SQL Fiddle here

Ok, worked fine until 10 rows, but beyond this, for example, 12 rows will show only the first 10. In this case, I need a total of 20 rows, 8 as null, i.e. multiples of 10. — deldev, Dec 19 '14 at 16:43
@dellasavia I'm sorry I cannot understand this. In my code @n defines the number of rows returned. Do you want sth else? — Giorgos Betsos, Dec 19 '14 at 16:50
Great! works for me with your update, but I had to do a little modification on `declare` statement and was like that: `DECLARE @n INT set @n = (select (total / 10 + 1) * 10 from (SELECT COUNT(*) as total FROM itens) a)` — deldev, Dec 19 '14 at 17:15
@dellasavia Glad we got it to work. You can check the final edit I made. Have also a look at the new version of SQL fiddle demo. It seems to work OK with 12 rows. — Giorgos Betsos, Dec 19 '14 at 17:18

score 0 · Answer 4 · answered Dec 19 '14 at 14:24

0

In the context of a proc/script, you can do your initial query into a table variable or temp table, check @@ROWCOUNT, or query the count of rows in the table, and then do a FOR loop to populate the rest of the rows. Finally, select * from your table variable/temp table.

answered Dec 19 '14 at 14:24

Tab Alleman

31,483
7
36
52

Looping is definitely not the best way to go here. A tally table is much more efficient than looping. – Sean Lange Dec 19 '14 at 14:29
Since the loop would run less than 10 times, I think the performance difference would be negligible. But, to be sure, there are many ways to get to the end result. – Tab Alleman Dec 19 '14 at 14:31
Sure in this case...but then somebody sees it and uses it again on a million rows because they don't know any better. Plus using a tally table is a lot simpler to write than using a loop to generate 10 rows. :D – Sean Lange Dec 19 '14 at 14:34

How can I get a sql query that always would be multiple of n?

4 Answers4

Linked