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