CODEWITHSUNDEEP
sqlsql-server-2008
Galih Leo
Galih Leo

Reputation: 55

How to add blank rows when select query sql

Example : when I type : 

select number, city from user

The results in the get is 3 rows. How to select the row that I can be automatically filled in 8 rows? What if using a loop ?

Desired Output:

if 3 rows then there are 5 empty rows,

if 4 rows then there are 4 empty rows, etc ..

Upvotes: 2

Views: 3572

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

While I don't understand the cause of this task, anyway you can do it like :

DECLARE @t TABLE ( ID INT )
DECLARE @c INT  = 8

INSERT  INTO @t
VALUES  ( 1 ),
        ( 2 ),
        ( 3 );
WITH    cte
          AS ( SELECT   1 AS rn
               UNION ALL
               SELECT   rn + 1
               FROM     cte
               WHERE    rn <= @c
             )
    SELECT TOP ( @c )
            *
    FROM    ( SELECT    ID
              FROM      @t
              UNION ALL
              SELECT    NULL
              FROM      cte
            ) t
    ORDER BY ID DESC

Output:

ID
3
2
1
NULL
NULL
NULL
NULL
NULL

Upvotes: 3

Related Questions