How can I get a sql query that always would be mul

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

Answer 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/

Answer 2

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

Answer 3

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.

Answer 4

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