question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked YitzhakKhabinsky-0887 commented

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

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-generalsql-server-transact-sql
image.png (2.9 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

whati try is
SELECT
PartFamilyId,
STUFF((
SELECT '| ' + ISNULL(FamilyStatus,'NULL')
FROM #partsFamily
WHERE (PartFamilyId = Results.PartFamilyId)
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,2,'') AS FamilyStatus
FROM #partsFamily Results
GROUP BY PartFamilyId

but it give me all result
what i need is to result below :

 PartFamilyId    FamilyStatus    
  3050            Active|NULL            
  5200            Active  




0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @ahmedsalah-1628,

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered YitzhakKhabinsky-0887 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.