I work on sql server 2014 I face issue I can't get only one row based on partid and compliance type id
and document type
so if have partid as 3581935 and compliance type id 1 and document type
Web Page OR Coc OR Contact
then first priority will be Web Page
second priority Coc
third priority Contact
so my sample as below :
create table FinalTableData(PartId int,Regulation nvarchar(300),
Comp_Status nvarchar(100),REVID int,Doc_Type nvarchar(20),
Document_Type int,ComplianceTypeID int)
insert into FinalTableData values(35819351,'RoHS (2015/863)','Compliant with Exemption',340434330,'Contact',1362938,1),
(35819351,'RoHS (2015/863)','Compliant',288530768,'Web Page',1232162,1),
(35819351,'RoHS (2015/863)','NotCompliant',288539070,'Coc',1232160,1),
(35819351,'REACH 2021 (219)','Compliant',340434330,'Contact',1362938,2),
(35819351,'TSCA','Compliant',340434352,'CoC',1232160,11),
(35819351,'TSCA','Compliant',340434330,'Contact',1362938,11)
my expected result :
PartId |
Regulation |
Comp_Status |
REVID |
Doc_Type |
Document_Type |
ComplianceTypeID |
35819351 |
RoHS (2015/863) |
Compliant |
288530768 |
Web Page |
1232162 |
1 |
35819351 |
REACH 2021 (219) |
Compliant |
340434330 |
Contact |
1362938 |
2 |
35819351 |
TSCA |
Compliant |
340434352 |
CoC |
1232160 |
11 |