sql server get missing records information

Question

Hi i have one doubt in sql server .
if any field donot have values then need to provide which filed donot have value
if data not available in one filed then output field value not available.
if data not available more than one filed then output those fields value are not available.
concate multiple fileds when values not exists.
sample data :
CREATE TABLE [dbo].[EmpDetails](
[Empid] [int] NULL,
[Empname] varchar NULL,
[Location] varchar NULL,
[Deptid] [int] NULL,
[Deptname] varchar NULL
)
INSERT [dbo].[EmpDetails] ([Empid], [Empname], [Location], [Deptid], [Deptname]) VALUES (1, NULL, N'che', 10, N'hr')
INSERT [dbo].[EmpDetails] ([Empid], [Empname], [Location], [Deptid], [Deptname]) VALUES (2, N'hari', N'pune', NULL, N'pm')
INSERT [dbo].[EmpDetails] ([Empid], [Empname], [Location], [Deptid], [Deptname]) VALUES (3, N'var', NULL, 30, NULL)
INSERT [dbo].[EmpDetails] ([Empid], [Empname], [Location], [Deptid], [Deptname]) VALUES (4, NULL, NULL, NULL, N'hr')
INSERT [dbo].[EmpDetails] ([Empid], [Empname], [Location], [Deptid], [Deptname]) VALUES (NULL, N'venu', N'pune', NULL, NULL)
INSERT [dbo].[EmpDetails] ([Empid], [Empname], [Location], [Deptid], [Deptname]) VALUES (NULL, N'kumar', N'pune', 20, NULL)
INSERT [dbo].[EmpDetails] ([Empid], [Empname], [Location], [Deptid], [Deptname]) VALUES (8, 'ravi', NULL, 10, N'hr')
INSERT [dbo].[EmpDetails] ([Empid], [Empname], [Location], [Deptid], [Deptname]) VALUES (10, N'k', N'pune', 20, N'hr')

based on above data I want output like below :
empid | Empname | Location | Deptid| Deptname | Validate
1 | NULL |Che |10 | hr | Empname value is not available
2 | hari |pune |NULL | pm | Deptid value is not available
3 | var |NULL |30 | NULL | location and deptname values are not available
4 | NULL |NULL |NULL | hr | empname and location and deptid values are not available
NULL | venu |pune |NULL | NULL | empid and deptid and deptname values are not available
NULL | kumar |pune |20 | NULL | empid and deptname values are not available
8 | ravi |NULL |10 | hr | location value is not available
10 | k |pune |20 | hr |

I tried like below :
select empid,empname,location,deptid,deptname,
case when coalesce(empid,'')='' then 'Empid' else '' end + ' '+
case when coalesce(empname,'')='' then 'Empname' else '' end + ' '+
case when coalesce(Location,'')='' then 'Location' else '' end + ' '+
case when coalesce(Deptid,'')='' then 'Deptid' else '' end +' '+
case when coalesce(Deptname,'')='' then 'Deptname' else '' end +' '+

• 'value not available' as Validate
  FROM [Test].[dbo].[EmpDetails]
  but above query not given expected format
  please tell me how to write query to achive this task in sql server

Answer 1

This is somewhat prettier:

; WITH CTE AS (
   select empid,empname,location,deptid,deptname,
   case when coalesce(empid,'')='' then 'Empid ' else '' end + ''+
   case when coalesce(empname,'')='' then 'Empname ' else '' end + ''+
   case when coalesce(Location,'')='' then 'Location ' else '' end + ''+
   case when coalesce(Deptid,'')='' then 'Deptid ' else '' end +''+
   case when coalesce(Deptname,'')='' then 'Deptname ' else '' end +''+
   + 'value not available' as Validate
   FROM [dbo].[EmpDetails] 
)
SELECT empid,empname,location,deptid,deptname, nullif(Validate, 'value not available')
FROM   CTE

Which version of SQL Server are you on?

Answer 2

Definitely not the cleanest, however if you are really picky about the different between "are" and "is" this is how I would do it without selecting a select statement...

select empid,empname,location,deptid,deptname,  
[Validate] = CONCAT(CONCAT_WS(' and ',  
case when coalesce(empid,'')='' then 'Empid' else NULL end,  
case when coalesce(empname,'')='' then 'Empname' else NULL end,  
case when coalesce(Location,'')='' then 'Location' else NULL end,  
case when coalesce(Deptid,'')='' then 'Deptid' else NULL end,  
case when coalesce(Deptname,'')='' then 'Deptname' else NULL end),  
case   
 (  
 case when isnull(empid,'')='' then 1 else 0 end +   
 case when isnull(empname,'')='' then 1 else 0 end +  
 case when isnull(Location,'')='' then 1 else 0 end +  
 case when isnull(Deptid,'')='' then 1 else 0 end +  
 case when isnull(Deptname,'')='' then 1 else 0 end  
 )  
when 1 then ' is not available'  
when 0 then NULL  
else ' are not available' end)  
FROM [dbo].[EmpDetails]  

Output:

If you want to take it a step further and replace some of the 'and's with commas...

select empid,empname,location,deptid,deptname,  
[Validate] = REPLACE(case when charindex(',',reverse(Validate))>0  
then  
reverse(replace(STUFF(reverse(Validate),charindex(',',reverse(Validate)),0,'#'),'#,',' dna '))  
else Validate end,'  ',' ')  
from  
(  
select empid,empname,location,deptid,deptname,  
[Validate] = CONCAT(CONCAT_WS(', ',  
case when coalesce(empid,'')='' then 'Empid' else NULL end,  
case when coalesce(empname,'')='' then 'Empname' else NULL end,  
case when coalesce(Location,'')='' then 'Location' else NULL end,  
case when coalesce(Deptid,'')='' then 'Deptid' else NULL end,  
case when coalesce(Deptname,'')='' then 'Deptname' else NULL end),  
case   
	(  
	case when isnull(empid,'')='' then 1 else 0 end +   
	case when isnull(empname,'')='' then 1 else 0 end +  
	case when isnull(Location,'')='' then 1 else 0 end +  
	case when isnull(Deptid,'')='' then 1 else 0 end +  
	case when isnull(Deptname,'')='' then 1 else 0 end  
	)  
when 1 then ' is not available'  
when 0 then NULL  
else ' are not available' end)  
FROM [dbo].[EmpDetails]  
)tbl  

Output:

Answer 3

Here is another solution, which I consider good enough for SQL 2012 and which evades the verb problem:

; WITH CTE AS (
   select Empid,Empname,Location,Deptid,Deptname,
   case when coalesce(Empid,'')='' then 'Empid ' else '' end + ''+
   case when coalesce(Empname,'')='' then 'Empname ' else '' end + ''+
   case when coalesce(Location,'')='' then 'Location ' else '' end + ''+
   case when coalesce(Deptid,'')='' then 'Deptid ' else '' end +''+
   case when coalesce(Deptname,'')='' then 'Deptname ' else '' end  AS missing
   FROM [dbo].[EmpDetails] 
)
SELECT Empid, Empname, Location, Deptid, Deptname, 'Missing value(s): ' + nullif(missing, '')
FROM   CTE

Answer 4

I used the spaces, since if you throw in commas, you will need to trim the last comma. But if you want commas:

; WITH CTE AS (
   select Empid,Empname,Location,Deptid,Deptname,
   case when coalesce(Empid,'')='' then 'Empid, ' else '' end + ''+
   case when coalesce(Empname,'')='' then 'Empname, ' else '' end + ''+
   case when coalesce(Location,'')='' then 'Location, ' else '' end + ''+
   case when coalesce(Deptid,'')='' then 'Deptid, ' else '' end +''+
   case when coalesce(Deptname,'')='' then 'Deptname, ' else '' end  AS missing
   FROM [dbo].[EmpDetails] 
)
SELECT Empid, Empname, Location, Deptid, Deptname, 
      'Missing value(s): ' + CASE WHEN len(missing) > 0
                                  THEN substring(missing, 1, len(missing) - 1)
                            END
FROM   CTE

Answer 5

Hi,@harinathu
Please also check this:

;WITH CTE1 AS   
(SELECT Empid,Empname,Location,Deptid,Deptname,  
        CASE WHEN Empid IS NULL THEN 'Empid, ' ELSE '' END +  
        CASE WHEN Empname IS NULL THEN 'Empname, ' ELSE '' END +  
        CASE WHEN Location IS NULL THEN 'Location, ' ELSE '' END +  
        CASE WHEN Deptid IS NULL THEN 'Deptid, ' ELSE '' END +  
        CASE WHEN Deptname IS NULL THEN 'Deptname, ' ELSE '' END  AS missing   
 FROM [dbo].[EmpDetails]   
),CTE2 AS  
(SELECT Empid, Empname, Location, Deptid, Deptname,   
       'Missing value(s): ' + CASE WHEN LEN(missing) > 0  
                                   THEN LEFT(missing,LEN(missing) - 1)  
                                   ELSE '' END AS missing   
 FROM  CTE1  
)  
 SELECT Empid, Empname, Location, Deptid, Deptname,  
 CASE WHEN CHARINDEX ( ',' , REVERSE(missing))>0   
      THEN REVERSE( STUFF ( REVERSE(missing), CHARINDEX ( ',' , REVERSE(missing)) ,1 ,'dna ') )  
      ELSE missing END AS [Missing Value(s)]  
 FROM CTE2  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.