Doonie Darkoo
Doonie Darkoo

Reputation: 1495

Add extra rows in result set of query with dummy data in sql server

I have this table let's say Students. If there are 2 students in table and result set gives 2 rows than I want to add 5 more rows with dummy data in it no matter what it says i.e 'Dummy Record' or something.

SELECT FirstName, (SELECT COUNT(*) FROM Student) Total
FROM Student

If the output is like this from above query.

FirstName         Total
Isaac Frempong    2
Erick Ortiz       2

I want the output to be like this

FirstName         Total
Isaac Frempong    2
Erick Ortiz       2
Dummy Data        2
Dummy Data        2
Dummy Data        2
Dummy Data        2
Dummy Data        2

I hope it's achievable, I'm unable to figure how to apply CASE or IF statements here. Maybe somebody could help.

Upvotes: 3

Views: 2378

Answers (2)

Kemal AL GAZZAH
Kemal AL GAZZAH

Reputation: 1047

Here is a code working on a table varaibale I called @students

Please replace it by your own table

I have filled this table with some Data, and tested the different cases (records number less then 7 or more than 7, and worked as you wish !)

1) First create a variable table called @mytable which will be filled by n records

n will be 7 if the number of records in your students table is equal or less than 7

n will be the number of records in your student table if it is more than 7

2) Then make a right outer join between your student table to which you add the record number using CTE ,row_number() function.

enter image description here

declare @students as table(id int identity(1,1),firstname nvarchar(50))

insert into @students(firstname) values
('Ali ben Hassine'),
('Mohamed el Aabed'),
('Ali ben Hassine'),
('Mohamed el Aabed'),
('Mohamed el Aabed'),
('Tahar Harbi'),
('Hassine Ayari'),
('Ihsen Trabelsi'),
('Marwa Mostari'),
('Mourad Zmerli'),
('Hafedh Gabsi'),
('Miloud Filali');

declare @mytab as table(n int)
declare @n as int
select @n=count(distinct(firstname))  from @students 
if @n<7 set @n=7
declare @i as int
set @i=1
while @i <@n+1
begin
insert into @mytab values(@i)
set  @i=@i+1
end;



with cte as
(select row_number() over(partition by 1 order by firstname) r#,firstname,count(1) Total from @students group by firstname)
select isnull(cte.firstname,'Dummy') firstanme,isnull(cte.total,2) Total from cte
right outer join @mytab t2 on cte.r#=t2.n
 [![enter image description here][1]][1]

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270391

One method is to use a VALUES() statement to construct the rows:

SELECT FirstName, COUNT(*) OVER () as Total
FROM Student
UNION ALL
SELECT 'Dummy', (SELECT COUNT(*)  FROM Student)
FROM (VALUES (1), (2), (3), (4), (5)) v(n);

EDIT:

If you always want 7 rows, use arithmetic:

SELECT FirstName, COUNT(*) OVER () as Total
FROM Student
UNION ALL
SELECT 'Dummy', s.total
FROM (VALUES (1), (2), (3), (4), (5), (6), (7)) v(n) CROSS JOIN
     (SELECT COUNT(*) as total FROM Student) s
WHERE v.n <= 7 - s.total;

s

Upvotes: 4

Related Questions