Reputation: 1495
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
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.
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
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