CODEWITHSUNDEEP
sqlsql-server
DeE DEe
DeE DEe

Reputation: 335

Add empty rows to results

I want to add empty rows to results fetched from a select statement. For example, if the select query fetch 4 rows then 2 empty rows needs to be fetched. Objective should be the number of rows fetched should be 6 every time. The number of rows fetched will be 6 maximum if there are 6 rows with data.

Any idea?

Upvotes: 3

Views: 4887

Answers (2)

Ashish Kumar
Ashish Kumar

Reputation: 157

Try the below logic:

with cte as
(
select 0 as col1
union all
select col1+1 from cte where cte.col1<10
)

select * into #temp1 from cte


create table #test
(rownum int,col1 varchar(100))


declare @i int=1
while (@i<=6)
begin

insert into #test
select *   from 
(select row_Number() over (order by (Select 0))rownum, * from #temp1)x
where rownum=@i 

Set @i=@i+1
end
select case when rownum>4 then '' else col1 end as col1 from #test

Upvotes: 0

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

In SQL-SERVER You can create temp table to update It with empty rows and you can use WHILE to insert desired number of rows with empty values. Something like:

-- Create temp table to update data with empty rows
CREATE TABLE #HoldEmptyRows
(
   Id NVARCHAR(20), 
   CustomerName NVARCHAR(20), 
   CustomerEmail NVARCHAR(20)
)

-- Insert data from SourceTable to temp
INSERT INTO #HoldEmptyRows
SELECT * FROM SourceTable

-- Do while count from temp table < of desired number insert empty rows
WHILE ((SELECT COUNT(*) cnt FROM #HoldEmptyRows) < 6)
BEGIN
   INSERT INTO #HoldEmptyRows VALUES ('', '', '')
END

SELECT * FROM #HoldEmptyRows

DEMO AT SQL FIDDLE

Upvotes: 2

Related Questions