Replace column value 0 (Zero) with Empty (Blank) in SQL Server

Last Reply one year ago By dharmendr

Posted one year ago

Hi! I used below script and got result:

Age        worker student               puple

11           0             0             1

21           0             1             0

24           1             0             0

31           2             0             0

My requirement is free cell instead 0:

Age        worker student               puple

11                                           1

21                           1            

24           1                            

31           2                            

USE [testDB]
GO
/****** Object:  Table [dbo].[KARTA]    Script Date: 29.11.2019 12:07:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[KARTA](
	[Kod] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nchar](20) NULL,
	[Birthday] [date] NULL,
	[Job] [nchar](20) NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[KARTA] ON 

INSERT [dbo].[KARTA] ([Kod], [Name], [Birthday], [Job]) VALUES (1, N'Rustam              ', CAST(N'1987-12-22' AS Date), N'1                   ')
INSERT [dbo].[KARTA] ([Kod], [Name], [Birthday], [Job]) VALUES (2, N'Muhammad            ', CAST(N'1994-12-08' AS Date), N'1                   ')
INSERT [dbo].[KARTA] ([Kod], [Name], [Birthday], [Job]) VALUES (3, N'Firuz               ', CAST(N'1998-04-19' AS Date), N'2                   ')
INSERT [dbo].[KARTA] ([Kod], [Name], [Birthday], [Job]) VALUES (4, N'Safar               ', CAST(N'2008-01-01' AS Date), N'3                   ')
INSERT [dbo].[KARTA] ([Kod], [Name], [Birthday], [Job]) VALUES (5, N'Nurullo             ', CAST(N'1987-12-22' AS Date), N'1                   ')
SET IDENTITY_INSERT [dbo].[KARTA] OFF

 

SELECT CONVERT(INT,DATEDIFF(DD,Birthday,GETDATE())/365.25) AS Age,
  COUNT(CASE K.JOB WHEN 1 THEN 1 END) worker,
  COUNT(CASE K.JOB WHEN 2 THEN 1 END) student,
  COUNT(CASE K.JOB WHEN 3 THEN 1 END) puple
  FROM KARTA K 
  GROUP BY CONVERT(INT,DATEDIFF(DD,Birthday,GETDATE())/365.25)
  ORDER BY CONVERT(INT,DATEDIFF(DD,Birthday,GETDATE())/365.25)

 

 

Posted one year ago

Hi PRA,

Use REPLACE frunction.

Refer below query.

SQL

DECLARE @KARTA AS TABLE(
	[Kod] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nchar](20) NULL,
	[Birthday] [datetime] NULL,
	[Job] [nchar](20) NULL
)

INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Rustam', CAST(N'1987-12-22' AS DateTime), N'1')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Muhammad', CAST(N'1994-12-08' AS DateTime), N'1')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Firuz', CAST(N'1998-04-19' AS DateTime), N'2')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Safar', CAST(N'2008-01-01' AS DateTime), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Nurullo', CAST(N'1987-12-22' AS DateTime), N'1')     

SELECT CONVERT(INT,DATEDIFF(DD,Birthday,GETDATE())/365.25) AS Age,
	REPLACE(COUNT(CASE K.JOB WHEN 1 THEN 1 END),0,'') worker,
	REPLACE(COUNT(CASE K.JOB WHEN 2 THEN 1 END),0,'') student,
	REPLACE(COUNT(CASE K.JOB WHEN 3 THEN 1 END),0,'') puple
FROM @KARTA K
GROUP BY CONVERT(INT,DATEDIFF(DD,Birthday,GETDATE())/365.25)
ORDER BY CONVERT(INT,DATEDIFF(DD,Birthday,GETDATE())/365.25)

Output

Age    worker    student    puple

11                                   1

21                       1    

24           1        

31           2