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