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

asked 2021-04-28T10:30:14.007+00:00
ahmed salah 3,126 Reputation points

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	  

92055-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,476 questions
{count} votes

Accepted answer
  1. answered 2021-04-28T14:41:07.767+00:00
    Yitzhak Khabinsky 19,846 Reputation points

    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           |  
    +--------------+------------------+  
    
    No comments

1 additional answer

Sort by: Most helpful
  1. answered 2021-04-28T21:19:22.997+00:00
    Erland Sommarskog 67,721 Reputation points Microsoft MVP

    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.