How to select PartFamilyId and FamilyStatus is active or (active and null) based

Question

How to select PartFamilyId and FamilyStatus is active or (active and null) based

ahmed_elbarbary.2010

Hall of Fame

Points: 3118
More actions
April 28, 2021 at 12:24 pm

#3876850
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
```
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)
```
what i need to do it :
select PartFamilyId,stuff(FamilyStatus) from #partsFamily group by PartFamilyId
Expected Result as following :
```
 PartFamilyId    FamilyStatus    
 3050            Active|NULL            
 5200            Active
```

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

select t1.PartFamilyId, t1.FamilyStatus + isnull('|' + ca.FamilyStatus,'') as FamilyStatus
from #partsFamily t1
outer apply (select 'NULL' from #partsFamily t2 where t2.PartFamilyId = t1.PartFamilyId and t2.FamilyStatus is null) ca(FamilyStatus)
where t1.FamilyStatus = 'Active'
and not exists(select * from #partsFamily t3 where t3.PartFamilyId = t1.PartFamilyId and t3.FamilyStatus<> t1.FamilyStatus);

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Adi Cohn SSC-Addicted Points: 424 More actions · Answer 2

One more way (so you'll have 2 options:-))

with PartFamiliesToUse as(
SELECT PartFamilyID
FROM #partsFamily
where FamilyStatus = 'Active'
EXCEPT 
SELECT PartFamilyID
FROM #partsFamily
WHERE FamilyStatus <> 'Active'),
Aggr as (
SELECT PFT.PartFamilyId, STUFF((SELECT '|' + isnull(PF.FamilyStatus, 'NULL')
FROM  #partsFamily PF WHERE PFT.PartFamilyId = PF.PartFamilyId
FOR XML PATH('')), 1, 1, '' ) Statuses
FROM PartFamiliesToUse PFT)
select * from Aggr

Adi