Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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

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 have tried:

what i try

SQL
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
Posted
Updated 10-Jan-22 9:22am
v2

1 solution

Try this query:
SELECT * 
FROM FinalTableData
ORDER BY
  case Doc_Type
  when 'Web Page' then 1
  when 'CoC' then 2
  when 'Contact' then 3
  else 4
  end
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900