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

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

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.
12,600 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,544 questions
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 24,911 Reputation points
    2021-04-28T14:41:07.767+00:00

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

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 100.8K Reputation points MVP
    2021-04-28T21:19:22.997+00:00

    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.