ahmed elbarbary

ahmed elbarbary

  • NA
  • 1.6k
  • 281.4k

How to select PartFamilyId and FamilyStatus is active or (active and n

Apr 28 2021 12:30 PM

How to select PartFamilyId and FamilyStatus is active or (active and null) based on partfamilyid concatenations by stuff ?

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 famulstatus active then it is ok i need it as 5200

if partfamily have famulstatus 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
  1. create table #partsFamily  
  2. (  
  3. PartFamilyId int,  
  4. FamilyStatus nvarchar(50),  
  5. CountStatus  int,    
  6. FamilyStatusStuff  nvarchar(2000)  
  7. )  
  8. insert into #partsFamily(PartFamilyId,FamilyStatus,CountStatusParts,FamilyStatusStuff)  
  9. values  
  10. (3000,'Obselete',5,NULL),  
  11. (3050,'Active',5,NULL),  
  12. (3050,NULL,2,NULL),  
  13. (3090,'Active',3,NULL),  
  14. (3090,'Obselete',4,NULL),  
  15. (4050,NULL,8,NULL),  
  16. (5200,'Active',2,NULL),  
  17. (5600,'Obselete',4,NULL),  
  18. (5600,'Pending',5,NULL)  
 

what i need to do it :

select PartFamilyId,stuff(FamilyStatus) from #partsFamily group by PartFamilyId

Expected Result as following :

  1. PartFamilyId    FamilyStatus      
  2.  3050            Active|NULL              
  3.  5200            Active   
92055-image.png 

Answers (6)