Select records if status column is active or null in SQL Server

ahmedsa
 
on Apr 28, 2021 10:42 PM
538 Views

I work on sql server 2012 I face issue I can't get partfamilyid that have familystatus active only or active and Null

so if partfamily have familystatus active then it is ok i need it as 5200

if partfamily have familystatus active and NULL then it is ok i need it as 3050

SO partfamilyid 5200 has familystatus Active so it is ok

and partfamilyid 3050 has familystatus Active and NULL so it is ok

any thing exception active only or active and null I don't need it
 
create table #partsFamily  
(  
    PartFamilyId int,  
    FamilyStatus nvarchar(50),  
    CountStatus  int,    
    FamilyStatusStuff  nvarchar(2000)  
)  
insert into #partsFamily(PartFamilyId,FamilyStatus,CountStatusParts,FamilyStatusStuff)  
values  
(3000,'Obselete',5,NULL),  
(3050,'Active',5,NULL),  
(3050,NULL,2,NULL),  
(3090,'Active',3,NULL),  
(3090,'Obselete',4,NULL),  
(4050,NULL,8,NULL),  
(5200,'Active',2,NULL),  
(5600,'Obselete',4,NULL),  
(5600,'Pending',5,NULL) 

Expected Result as following :

  1. PartFamilyId    FamilyStatus      
  2.  3050            Active|NULL              
  3.  5200            Active  
Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Apr 29, 2021 01:58 AM

Hi ahmedsa,

Refer below query.

SQL

create table #partsFamily  
(  
	PartFamilyId int,  
	FamilyStatus nvarchar(50),  
	CountStatus  int,    
	FamilyStatusStuff  nvarchar(2000)  
)  
insert into #partsFamily(PartFamilyId,FamilyStatus,CountStatus,FamilyStatusStuff)  
values  
(3000,'Obselete',5,NULL),  
(3050,'Active',5,NULL),  
(3050,NULL,2,NULL),  
(3090,'Active',3,NULL),  
(3090,'Obselete',4,NULL),  
(4050,NULL,8,NULL),  
(5200,'Active',2,NULL),  
(5600,'Obselete',4,NULL),  
(5600,'Pending',5,NULL)  


SELECT DISTINCT * FROM 
(
	SELECT PartFamilyId,
	STUFF(( SELECT '| ' + ISNULL(FamilyStatus,'NULL')
	        FROM #partsFamily
	        WHERE (PartFamilyId = pf.PartFamilyId)
	        FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,2,'') AS FamilyStatus
	FROM #partsFamily pf
) t
WHERE 
t.FamilyStatus like '%Active%' AND t.FamilyStatus NOT like '%Obselete%'

DROP TABLE #partsFamily

Output

PartFamilyId FamilyStatus

      3050         Active| NULL

      5200         Active