In SQL replace null value with another value

Question

Here is my SQL Query

SELECT p.StudentID, ai.RollNo, p.FirstName, p.MiddleName, p.LastName, 
om.ExamID,  et.ExamName, om.SubjectID,
ISNULL(CONVERT(varchar(20),om.ObtainedMarksTheory), 'A') as 'ObtainedMarksTheory', 
ISNULL(CONVERT(varchar(20),om.ObtainedPracticalMarks),'A') as 'ObtainedPracticalMarks'
FROM Students.PersonalInfo p
INNER JOIN Students.AcademicCourse ac on p.StudentID = ac.StudentID
INNER JOIN Students.AcademicInfo ai on p.StudentID=ai.StudentID
LEFT OUTER JOIN Exam.ObtainedMarkEntry om on p.StudentID = om.StudentID
LEFT JOIN Exam.ExamType et   on om.ExamID = et.ExamID
WHERE ai.BatchID = '103' AND ai.SemesterID = '21' and ac.Section = '8'

This produce result as in a picture:

But I want result like this since those two students were absent in that exam

Similarly if another Exam does exists for any of three student and other are absent same procedure should repeat

Can you please explain the logic behind the output your trying to achieve? where are those "default" values coming from? — Amit
– Amit, Commented Jul 23, 2015 at 11:15
In this particular result set student are enrolled in exam. In above result student with rollno 1 has attended Exam with ExamID 28 but rest of the two have not attended it but the exam was compulsory. So that ExamID, ExamName, SubjectID field should populate to those students too who didn't attended. Rest of two columns with value A indicates absent. — Bhuban Shrestha
– Bhuban Shrestha, Commented Jul 23, 2015 at 11:34
Something wrong happened with the images. They too became NULL — Kartic
– Kartic, Commented Jul 23, 2015 at 11:42

Shahzad Riaz · Accepted Answer · 2015-07-23 11:32:53Z

1

Use IsNULL() Function see below example

Declare @variable varchar(MAX)
set @variable = NULL
select IsNULL(@variable,0) as A

edited Jul 23, 2015 at 11:32

answered Jul 23, 2015 at 11:27

Shahzad Riaz

3521 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bhuban Shrestha Over a year ago

I think I simply can't do this. because isnull replace null field with some known static value in this case 0 but In my case I don't know with what value I have to replace that null field. But what I know is; a field which contains value , that value should pass to null fields

Fabio Over a year ago

@user42392, use ISNULL(ColumnContainedNULL, ColumnWhichReplaceNULLValue)

gotqn · Accepted Answer · 2015-07-23 11:34:39Z

0

Let's have the following sample data (it is like your sample data, just inserted in one table):

DECLARE @DataSource TABLE
(
    [StudentID] TINYINT
   ,[RowNo] TINYINT
   ,[FirstName] VARCHAR(12)
   ,[MiddleName] VARCHAR(12)
   ,[LastName] VARCHAR(12)
   ,[ExamID] TINYINT
   ,[ExamName] VARCHAR(18)
   ,[SubjectID] TINYINT
   ,[ObtainedMarksTheory] VARCHAR(12)
   ,[ObtainedPracticalMarks] VARCHAR(12)
);

INSERT INTO @DataSource ([StudentID], [RowNo], [FirstName], [MiddleName], [LastName], [ExamID], [ExamName], [SubjectID], [ObtainedMarksTheory], [ObtainedPracticalMarks])
VALUES (101, 1, 'FN_A', 'MN_A', 'LN_A', NULL, NULL, NULL, 'A', 'A')
      ,(102, 2, 'FN_B', 'MN_B', 'LN_B', 28, 'First Tem2072', 97, '74.00', '56.00')
      ,(103, 3, 'FN_C', 'MN_C', 'LN_C', NULL, NULL, NULL, 'A', 'A');

SELECT *
FROM @DataSource;

So, I am supposing we have details only for one exam here and the question is how to replace the NULL values of ExamID, ExamName and SubjectID columns using the existing values.

The solution is to use the MAX function with OVER clause:

SELECT [StudentID]
      ,[RowNo]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,MAX([ExamID]) OVER() AS [ExamID]
      ,MAX([ExamName]) OVER() AS [ExamName]
      ,MAX([SubjectID]) OVER() AS [SubjectID]
      ,[ObtainedMarksTheory]
      ,[ObtainedPracticalMarks]
FROM @DataSource;

Note, that it is better to use the OVER clause as you are not going to add GROUP BY clauses in your initial query. Also, if you have details for more exams you can use the PARTITION BY clause (if you have some column(s) to distinguish the NULL rows for each exam).

answered Jul 23, 2015 at 11:34

gotqn

44k47 gold badges167 silver badges257 bronze badges

2 Comments

Bhuban Shrestha Over a year ago

Ok I will try this in my query. And yes I have to show more exam results too.

gotqn Over a year ago

@user42392 OK, you need to add to your sample data a column or columns by which to distinguish the exams. Another solution will be to order the results by some columns. Could you edit your question and add such data/output?

Collectives™ on Stack Overflow

In SQL replace null value with another value

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related