Post
Ask Question
ahmed elbarbary

ahmed elbarbary

  • NA
  • 1.6k
  • 254.9k

How to get data from table Compliance Data Horizontally Based on Part

Dec 29 2021 12:32 AM

I work on SQL server 2014 I need to get data from compliance data table horizontally .

based on part id and compliance type Id

every part id will have one row per 3 compliance type

every row per part will have 3 compliance type id 1,2,11

if part not have 3 compliance then it will take Null on empty compliance Type

as part id 749120,4620

part id 749120 blank on reach and TSKA

part id 4620 blank on TSKA

if I have multi row per both ( compliance type and part id ) as part id 5090

then I will take first row based on max version order as 40 for part id 5090

version order 40 is bigger than 3 so I take first row version order 40 Per Compliance Type Id 1

every compliance Type not have data will be display as NULL on data related as

Part id 749120 will have NULL ON Rohs and TSKA

so How to do that please ?

sample data as below

and below sample expected result

create table #ComplianceData(PartId int,ComplianceTypeID int,CompStatus nvarchar(30),VersionOrder int,ComplianceType NVARCHAR(30))insert into #ComplianceData(PartId,ComplianceTypeID,CompStatus,VersionOrder,ComplianceType)values(5090,1,'Compliant',3,'Rohs'),(5090,1,'NotCompliant',40,'Rohs'),(5090,2,'Compliant',25,'Reach'),(5090,11,'NotCompliant',1,'TSKA'),(2306,1,'Compliant',3,'Rohs'),(2306,2,'NotCompliant',25,'Reach'),(2306,11,'Compliant',1,'TSKA'),(4620,1,'NotCompliant',3,'Rohs'),(4620,2,'Compliant',25,'Reach'),(749120,2,'Compliant',25,'Reach')


Answers (4)