How to get duplicate on PartId and Company Id both and have different

Question

How to get duplicate on Part Id and Company Id both and have different Status ?

I work on SQL server 2012 I have issue I can't get duplicate on company id and part id

and on same time have different status

as partid 1211 and companyid 3030 repeated two times meaning two rows but with different status

pending and Done

so How to write query do that

create table #PartsData
(
PartId int,
CompanyId int,
Status nvarchar(50)
)
insert into #PartsData(PartId,CompanyId,Status)
values
(1211,3030,'Pending'),
(1211,3030,'Done'),
(1599,4812,'NotFound'),
(1599,4812,'Pending'),
(9731,4050,'Inprogress'),
(9731,4050,'Done'),
(7801,4031,'Pending'),
(7822,9815,'Pending')
Expected result :
PartId CompanyId Status
1211 3030 Pending
1211 3030 Done
1599 4812 NotFound
1599 4812 Pending
9731 4050 Inprogress
9731 4050 Done

Amit Gupta · Answer

If you are inserting duplicate records then you might be in trouble deleting it, you should normalize it. Anyway putting distinct keyword will fetch you the desired output SELECT DISTINCT * FROM #PartsData WHERE PartId IN ( SELECT PartId FROM #PartsData GROUP BY partid HAVING COUNT (*) > 1 ) ORDER BY PartId

Amit Gupta · Answer

Try this SELECT * FROM #PartsData WHERE PartId IN ( SELECT PartId FROM #PartsData GROUP BY partid HAVING COUNT(*) > 1 ) ORDER BY PartId output:

ahmed elbarbary · Answer

thank you for reply code above will get repeated rows per companyid and partid and this I need but i need also different status meaning I Need below because repeated and different status PartId CompanyId Status 1211 3030 Pending 1211 3030 Done and I don't need below because repeated and same status PartId CompanyId Status 1599 4812 NotFound 1599 4812 NotFound

Jignesh Kumar · Answer

Hi, If you have only these 3 columns then use distinct, select distinct * from #PartsData

Ajay Gohil · Answer

Hello, You can try the following code: select * from PartsData where PartId=1211; Note that PartsData doesn't have a primary key.

ahmed elbarbary · Answer

thank you for reply but i need repeated same partid and company id but different status and that will give me also status similar and I need different status

Amit Gupta · Answer

SELECT * FROM PartsData group by PartsId, CompanyId, Status having Count(*)>1