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

Question

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	  

Answer 1

Hi @ahmed salah ,

Please try the following solutions.

• Method #1 is more simple, but not generic. It is dependent on the sequential order of 'Active' and 'NULL' values.
• Method #2 is more generic, i.e. sequential order of 'Active' and 'NULL' values is irrelevant. It is using XQuery's Quantified Expressions.

SQL

-- DDL and sample data population, start  
DECLARE @partsFamily table (PartFamilyId int, FamilyStatus nvarchar(50), CountStatus  int, FamilyStatusStuff  nvarchar(2000));  
INSERT INTO @partsFamily (PartFamilyId,FamilyStatus,CountStatus, FamilyStatusStuff) VALUES  
(3000,'Obsolete',5,NULL),  
(3050,'Active',5,NULL),  
(3050,NULL,2,NULL),  
(3090,'Active',3,NULL),  
(3090,'Obsolete',4,NULL),  
(4050,NULL,8,NULL),  
(5200,'Active',2,NULL),  
(5600,'Obsolete',4,NULL),  
(5600,'Pending',5,NULL);  
-- DDL and sample data population, end  
  
SELECT * FROM @partsFamily;  
  
-- Method #1  
;WITH rs AS  
(  
 SELECT PartFamilyId  
 , STUFF((  
 SELECT ' | ' + COALESCE(FamilyStatus,'NULL')  
 FROM @partsFamily AS c  
 WHERE (c.PartFamilyId = p.PartFamilyId)  
 FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')  
 ,1,3,'') AS FamilyStatusList  
 FROM @partsFamily AS p  
 GROUP BY PartFamilyId  
)  
SELECT * FROM rs  
WHERE FamilyStatusList IN ('Active', 'Active | NULL');  
  
-- Method #2  
-- XQuery  
DECLARE @target NVARCHAR(MAX) = TRY_CAST('<target><r>Active</r><r>NULL</r></target>' AS NVARCHAR(MAX));  
  
;WITH rs AS  
(  
 SELECT p.PartFamilyId  
    , TRY_CAST('<root>' +   
    TRY_CAST((  
    SELECT COALESCE(FamilyStatus,'NULL') AS r  
    FROM @partsFamily AS c  
    WHERE c.PartFamilyId = p.PartFamilyId  
    FOR XML PATH(''), TYPE, ROOT('source')  
    ) AS NVARCHAR(MAX)) + @target + '</root>' AS XML) AS xmldata  
 FROM @partsFamily AS p  
 GROUP BY p.PartFamilyId  
)  
SELECT PartFamilyId   
 , xmldata.query('data(/root/source/r)').value('.', 'VARCHAR(MAX)') AS FamilyStatusList  
FROM rs  
WHERE xmldata.value('every $x in /root/source/r/text()  
            satisfies ($x = (/root/target/r/text())  
           and not(count(/root/source/r) eq 1 and /root/source/r/text()[1] = "NULL"))', 'BIT') = 1;  

Output

+--------------+------------------+  
| PartFamilyId | FamilyStatusList |  
+--------------+------------------+  
|         3050 | Active | NULL    |  
|         5200 | Active           |  
+--------------+------------------+  

Answer 2

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

Stuff is not for string concatenation. Stuff is a function that permits you to add and/or remove characters in a string. Many people use stuff to wrap the string-concatenation operation with FOR XML PATH, since this operation produces a trailing delimiter.

Personally, I dislike this, because I think the resulting code has too much at the same time, and I prefer to remove the trailing comma with substring in a separate step.