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

Transact-SQL
, ,
1

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
2

I dont mean to be rude or mean but the content of your posts seem to indicate to me that you might be doing some consulting /contract work. I say that because your posts contain tables that are in totally different domains. You have been posting questions for over a year now and your contribution to this forum is strictly questions. I dont think others can/should do the work you are hired to do? At this point of your career, at least in the time spent in this forum, I think you should be able to sort these questions out by yourself and even contribute to this forum by providing answers to others that post questions? Maybe some training you could take? This is to help you grow, I am not trying to chastise you.

3 Likes
3

this is what i try

 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
INTO #tmp_partsFamily
FROM #partsFamily Results
GROUP BY PartFamilyId
SELECT PartFamilyId,FamilyStatus
FROM   #tmp_partsFamily
WHERE FamilyStatus LIKE '%Active%'

but still ave issue as below :slight_smile:

|PartFamilyId|FamilyStatus|
|---|---|
|3050| Active | NULL|
|3090| Active | Obselete|
|5200| Active|

but i can't display null with active so correct result must be :slight_smile:

PartFamilyId FamilyStatus
3050 Active|NULL
5200 Active

4 
SELECT
PartFamilyId,
STUFF((
SELECT ' | ' + ISNULL(FamilyStatus,'NULL')
FROM #partsFamily
WHERE (PartFamilyId = Results.PartFamilyId)
ORDER BY ISNULL(FamilyStatus,'NULL')
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,3,'') AS FamilyStatus
FROM #partsFamily Results
GROUP BY PartFamilyId
HAVING MAX(CASE WHEN FamilyStatus IS NULL OR FamilyStatus = 'Active' THEN 0 ELSE 1 END) = 0
5

thank you for reply

|PartFamilyId|FamilyStatus|
|---|---|
|3050|Active | NULL|
|4050|NULL|
|5200|Active|

still give me wrong result
4050 is wrong
i need parts family is active and null or active only

correct result must be
PartFamilyId FamilyStatus
3050 Active|NULL
5200 Active

6

Then change the query to fix that.

1 Like
7

Here's the query with the change:

SELECT
PartFamilyId,
STUFF((
SELECT ' | ' + ISNULL(FamilyStatus,'NULL')
FROM #partsFamily
WHERE (PartFamilyId = Results.PartFamilyId)
ORDER BY ISNULL(FamilyStatus,'NULL')
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,3,'') AS FamilyStatus
FROM #partsFamily Results
GROUP BY PartFamilyId
HAVING MAX(CASE WHEN FamilyStatus = 'Active' THEN 1 ELSE 0 END) = 1 AND
MAX(CASE WHEN FamilyStatus IS NULL OR FamilyStatus = 'Active' THEN 0 ELSE 1 END) = 0
1 Like