How to select PartFamilyId and FamilyStatus is active or (active and n

ahmed elbarbary

5y801

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

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

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar5y ago
Accepted answer
1. SELECT
2. w.PartFamilyId,
3. count(*) as cnt,
4. stuff(
5. (
6. select '|' + ISNULL(convert(varchar(10),b.FamilyStatus),'NULL')
7. from #partsFamily b
8. where b.PartFamilyId = w.PartFamilyId
9. FOR XML PATH('')
10. ), 1, 1, ''
11. ) as FamilyStatus
12. INTO temp
13. from #partsFamily w
14. group by w.PartFamilyId
16. SELECT PartFamilyId,FamilyStatus FROM temp WHERE FamilyStatus='Active|NULL' OR FamilyStatus='Active'
+1

Shubham Chaudhari5y ago

--GET ONLY 2 result 5200, 3050

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' OR

(FamilyStatus like '%Active%' AND FamilyStatus like '%NULL%')

ahmed elbarbary5y ago

thank you for reply

still not correct i get following result

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

correct result must be 3050 and 5200

because as first post i need partfamily that is active only or active with NULL

so correct result must be

PartFamilyId FamilyStatus
3050 Active|NULL
5200 Active

Shubham Chaudhari5y ago

--Update with temp table

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%'

ahmed elbarbary5y ago

thank you for reply

this is give me all result

but what i need actually

PartFamilyId FamilyStatus
3050 Active|NULL
5200 Active

meaning i need any family have active status only or active with null

Shubham Chaudhari5y ago

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