Insert empty rows in select result if number of rows is under a certain amount-CodePudding

I'm having an issue with a report i need to build for a customer with Crystal Report 2016. I must build a select in MSSQL where it may return up to 10 rows. But here is the problem, when less than 10 results are returned, i still need to have 10 rows. So I were thinking add empty values to specific columns, but can't find any useful information in the documentation. Been told that can be achieved using a stored procedure, but my company is not allowed to add anything to the customer's database.

Beside stored procedures, is there a way to achieve it?

CodePudding user response：

One solution would be to create a "numbers table" as either a CTE or derived table, for numbers 1-10, and then make sure that your query has some kind of line number. Then just LEFT OUTER JOIN to your query.

Something like this:

WITH NUMBERS AS
(
SELECT 1 LINE
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9
UNION ALL
SELECT 10
)

SELECT YOURTABLE.* 
    FROM NUMBERS
        LEFT OUTER JOIN YOURTABLE
            ON YOURTABLE.LINE = NUMBERS.LINE

There might be something slightly more elegant but that should work...