How to get first row group by partid and compliance typeid and document type?

Transact-SQL
, ,
1

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,
Row_Number int,
Regulation nvarchar(300),
Comp_Status  nvarchar(100),
REVID	int,
Doc_Type nvarchar(20),
Document_Type int,
ComplianceTypeID int
)
insert into FinalTableData

values
(35819351,1,'RoHS (2015/863)','Compliant with Exemption',340434330,'Contact',1362938,1),
(35819351,2,'RoHS (2015/863)','Compliant',288530768,'Web Page',1232162,1),
(35819351,3,'RoHS (2015/863)','NotCompliant',288539070,'Coc',1232160,1),
(35819351,1,'REACH 2021 (219)','Compliant',340434330,'Contact',1362938,2),
(35819351,1,'TSCA','Compliant',340434352,'CoC',1232160,11),
(35819351,2,'TSCA','Compliant',340434330,'Contact',1362938,11)

expected result as below

partid RohsCompliance reachCompliance Rohs_SourceType SVHCStatus RohsRegulation ReachRegulation Reach_SourceType Reach_Revision_ID TSKA_Revision_ID TSKAStatus TSKA_SourceType
35819351 1 2 Web Page Compliant Compliant Compliant Contact 340434330 340434352 Compliant CoC

what i try

CREATE TABLE #TempTable
(
PartId int
)
insert into #TempTable(PartId)
select 35819351
SELECT 
md.partid,
rohs.ComplianceTypeID as RohsCompliance,
reach.ComplianceTypeID as reachCompliance,
	
			

	       
			Rohs.Doc_Type AS Rohs_SourceType,     -- Rohs Doc Type
			
		    --=========================================Reach  
			Reach.Comp_Status AS SVHCStatus,
			case when Rohs.Regulation like '%2015%' then Rohs.Comp_Status  else 'Unknown (Old Version Status)' end AS RohsRegulation,
			case when Reach.Regulation like '%219%' then Reach.Comp_Status  else 'Unknown (Old Version Status)' end AS ReachRegulation,
			--case when Reach.RegID=190 then Reach.Comp_Status  else 'Unknown (Old Version Status)' end AS ReachRegulation,
			Reach.Doc_Type AS Reach_SourceType,
			case when Reach.REVID is null then 9070 else Reach.REVID end  AS Reach_Revision_ID,
			case when TSKA.REVID is null then  7050 else TSKA.REVID end  AS TSKA_Revision_ID,
			TSKA.Comp_Status AS TSKAStatus ,
			TSKA.Doc_Type AS TSKA_SourceType 

		
		FROM #TempTable MD

	    LEFT OUTER JOIN FinalTableData Rohs ON MD.PartID =Rohs.PartID AND Rohs.ComplianceTypeID=1 AND Rohs.Row_Number=1
		LEFT OUTER JOIN FinalTableData Reach ON MD.PartID =Reach.PartID AND Reach.ComplianceTypeID=2 AND Reach.Row_Number=1
		LEFT OUTER JOIN FinalTableData TSKA ON MD.PartID =TSKA.PartID AND TSKA.ComplianceTypeID=11 AND TSKA.Row_Number=1
2

You have asked many questions and people have spent time out to answer them, Please respond to those first before posting?

3

Also, the one I replied with is also the answer to this one if you stretch your own imagination just a little bit.

1 Like
4

thank you all for support and help
i solved my issue by
use
SELECT *
FROM FinalTableData
ORDER BY
case Doc_Type
when 'Web Page' then 1
when 'CoC' then 2
when 'Contact' then 3
else 4
end