Return data even if no record present

• sindhupavani123

  Right there with Babe

  Points: 754

  More actions

  December 5, 2018 at 4:35 am

  #369221

  Hi all,
  I have table called students, and I would get record even if data is not present for a particular course , in particular year.

  Here is my data :

  CREATE TABLE students
  (
  Year int,
  Course varchar(50),
  Number of students int
  )

  INSERT INTO students
  VALUES (2018,’Maths ‘, 10),
                  (2019,’Maths’,15),
                  (2018,’Economics’,8)
                  (2018,’Social Studies’,3)
                  (2019,’Social Studies’, 4)
                  (2018, ‘Science’,7)
  How to get output like this :

  Year	Course	Students
  2018	Maths	10
  2019	Maths	15
  2018	Economics	8
  2019	Economics	0
  2018	Social Studies	3
  2019	Social Studies	4
  2018	Science	7
  2019	Science	0

  Please I don’t want to write code values manually if using where clause , example, where course in (‘economics ‘,’maths’ ). Would like to use variables instead in SQL server.

  Thank you.

• Jonathan AC Roberts

  SSCoach

  Points: 18356

  More actions

  December 5, 2018 at 4:48 am

  #2015546

  If you set your variable to a csv string e.g.:
  DECLARE @Subjects as nvarchar(100) = 'economics,maths'
  Then use a string splitter function to convert search value into a row you can join on.
  Look up DelimitedSplit8K .
  DECLARE @Subjects as nvarchar(100) = 'Economics,Maths'
select s.*
from students s
inner join dbo.DelimitedSplit8K(@Subjects, ',') sub
ON sub.Item=s.Course

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  December 5, 2018 at 9:56 am

  #2015579

  sindhupavani123 - Wednesday, December 5, 2018 4:35 AM

  Hi all,
  I have table called students, and I would get record even if data is not present for a particular course , in particular year.

  Here is my data :

  CREATE TABLE students
  (
  Year int,
  Course varchar(50),
  Number of students int
  )

  INSERT INTO students
  VALUES (2018,’Maths ‘, 10),
                  (2019,’Maths’,15),
                  (2018,’Economics’,8)
                  (2018,’Social Studies’,3)
                  (2019,’Social Studies’, 4)
                  (2018, ‘Science’,7)
  How to get output like this :

  Year	Course	Students
  2018	Maths	10
  2019	Maths	15
  2018	Economics	8
  2019	Economics	0
  2018	Social Studies	3
  2019	Social Studies	4
  2018	Science	7
  2019	Science	0

  Please I don’t want to write code values manually if using where clause , example, where course in (‘economics ‘,’maths’ ). Would like to use variables instead in SQL server.

  Thank you.

  Can you post the query that you're having problems with, please?  You say "year" in your description of what you want to do but then your example lists multiple years.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 5, 2018 at 11:03 am

  #2015587

  Something like this perhaps:
  
if object_id('[dbo].[students]','U') is not null
drop table [dbo].[students];

  create table [dbo].[students]
(
  [Year] int
, [Course] varchar(50)
, [NumberOfstudents] int
);

  insert into [dbo].[students]
values
(2018, 'Maths', 10)
, (2019, 'Maths', 15)
, (2018, 'Economics', 8)
, (2018, 'Social Studies', 3)
, (2019, 'Social Studies', 4)
, (2018, 'Science', 7);
go

  with SchoolYears as (
select distinct
[Year]
from
[dbo].[students])
, Courses as (
select distinct
[Course]
from
[dbo].[students])
select
[sy].[Year]
, [crs].[Course]
, [NumberOfstudents] = isnull([stu].[NumberOfstudents],0)
from
[SchoolYears] as sy
cross apply [Courses] as crs
left outer join [dbo].[students] as stu
  on [sy].[Year] = [stu].[Year]
   and [crs].[Course] = [stu].[Course]
order by [Course], [Year];
go