how to display value "NULL" where mark is below 50

Hope this helps you --

CREATE TABLE #MarksTemporary

  (  

     ID INT IDENTITY  

   , StudentID INT  

   , StudentName VARCHAR(100)

   , Marks INT     

  )  





 INSERT INTO #MarksTemporary

         ( 

           StudentID ,

           StudentName ,

           Marks

         )

 SELECT StudentID,StudentName,NULL from StudentDetails WHERE Marks <= 25

Please mark as answer if this content helps you.

Thanks
Tanuja
(If this content helped you, Please mark it as Answer)

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Hi Gow.Net, As i am beginner in sql server. this might not be an optimized code.
here i am presenting a solution to your example, this might help you:

To script a new temporary table same as the result table. Here in result table, it should be allow nulls for the marks column.. because you want to display a null value for the marks less than (<=) 50. Here is my table schema:

Result:

CREATE TABLE [dbo].[RESULT](

	[REGNO] [int] NOT NULL,

	[NAME] [nvarchar](50) NOT NULL,

	[DEPT] [nvarchar](50) NOT NULL,

	[MARK1] [int] NULL,

	[MARK2] [int] NULL,

	[MARK3] [int] NULL,

	[MARK4] [int] NULL,

	[MARK5] [int] NULL,

	[MARK6] [int] NULL,

	[TOTAL] [int] NULL,

	[STATUS] [nvarchar](50) NOT NULL

)

Here i have a row as

Eg: 1	VASANTH	DOT NET	50	100	100	70	50	35	500	PASS in the result table.

So, the idea is to script the same table as a temporary table and update the values where the marks are less than 50 with null value with the following query.

SELECT * INTO RESULTNEW FROM RESULT // TO CREATE A TEMPORARY TABLE(RESULTNEW) SAME AS RESULT

TO UPDATE THE VALUES

BEGIN

DECLARE



@MARK1 INT,

@MARK2 INT,

@MARK3 INT,

@MARK4 INT,

@MARK5 INT,

@MARK6 INT



SELECT @MARK1 = MARK1 FROM RESULTNEW WHERE REGNO = 1

IF(@MARK1 <= 50)

BEGIN

SET @MARK1 = NULL

END

SELECT @MARK2 = MARK2 FROM RESULTNEW WHERE REGNO = 1

IF(@MARK2 <= 50)

BEGIN

SET @MARK2 = NULL

END

SELECT @MARK3 = MARK3 FROM RESULTNEW WHERE REGNO = 1

IF(@MARK3 <= 50)

BEGIN

SET @MARK3 = NULL

END

SELECT @MARK4 = MARK4 FROM RESULTNEW WHERE REGNO = 1

IF(@MARK4 <= 50)

BEGIN

SET @MARK4 = NULL

END

SELECT @MARK5 = MARK5 FROM RESULTNEW WHERE REGNO = 1

IF(@MARK5 <= 50)

BEGIN

SET @MARK5 = NULL

END

SELECT @MARK6 = MARK6 FROM RESULTNEW WHERE REGNO = 1

IF(@MARK6 <= 50)

BEGIN

SET @MARK6 = NULL

END





UPDATE RESULTNEW SET MARK1 = @MARK1, MARK2 = @MARK2, MARK3 = @MARK3, MARK4 = @MARK4, MARK5 = @MARK5, MARK6 = @MARK6

END

After executing the query, my ResultNew table has:

1	VASANTH	DOT NET	NULL	100	100	70	NULL	NULL	500	PASS

Please correct me if there is a better procedure.
Regards,

Awesome Coding !! :)

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

try this query

SELECT CASE WHEN [mark1] < 50 THEN '' WHEN [mark1] > 49 THEN [mark1] From YourTableName

you can add more columns with CASE as per ur requirment.

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

hi

First change your table script here

[mark1] [nvarchar](20) NOT NULL,

[mark2] [nvarchar](20) NOT NULL,

[mark3] [nvarchar](20) NOT NULL,

[mark4] [nvarchar](20) NOT NULL,

[mark5] [nvarchar](20) NOT NULL,

[mark6] [nvarchar](20) NOT NULL,

remove not null becos your table allow null value after use this code here i have put null for 0 populated

Select regno,name,dept,ISNULL(Mark1,0),ISNULL(Mark2,0),ISNULL(Mark3,0),ISNULL(Mark4,0),ISNULL(Mark5,0),ISNULL(Mark6,0) from result

where Mark1<50 and Mark2<50 and Mark3<50 and Mark4<50 and Mark5<50 and Mark6<50

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

hi
Please mark as answer if it helpful to you.

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator