How to get data from table Compliance Data Based on PartId and ComplianceTypeId?

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  December 29, 2021 at 12:26 am

  #3967991

  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

  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')

  Expected result :

  

  • This topic was modified 2 years, 11 months ago by  ahmed_elbarbary.2010. Reason: more clear data

• December 29, 2021 at 9:51 am

  This was removed by the editor as SPAM

• Steve Collins

  SSCrazy

  Points: 2157

  More actions

  December 29, 2021 at 6:33 pm

  #3968223

  On first read it's not very clear.  Having the sample data and expected results makes all the difference.   Not sure about the ORDER BY tho

   with
   Parts_cte(PartId) as (
      select distinct PartId
      from #ComplianceData),
   ComplianceTypes_cte(ComplianceTypeID, ComplianceType) as (
      select *
      from (values (1, 'Rohs'),
                   (2, 'Reach'),
                   (11,'TSKA')) v(ComplianceTypeID, ComplianceType)),
  ComplianceData_cte as (
      select top 1 with ties *
      from #ComplianceData
      order by row_number() over (partition by PartId, ComplianceTypeID 
                                      order by VersionOrder desc))
  select p.PartId,
         max(case when ct.ComplianceTypeID=1 then cd.ComplianceTypeID else null end) RohsComplianceTypeID,
         max(case when ct.ComplianceTypeID=1 then cd.CompStatus else null end) RohsCompStatusd,
         max(case when ct.ComplianceTypeID=1 then cd.VersionOrder else null end) RohsVersionOrder,
         max(case when ct.ComplianceTypeID=2 then cd.ComplianceTypeID else null end) ReachComplianceTypeID,
         max(case when ct.ComplianceTypeID=2 then cd.CompStatus else null end) ReachCompStatusd,
         max(case when ct.ComplianceTypeID=2 then cd.VersionOrder else null end) ReachVersionOrder,
         max(case when ct.ComplianceTypeID=11 then cd.ComplianceTypeID else null end) TSKAComplianceTypeID,
         max(case when ct.ComplianceTypeID=11 then cd.CompStatus else null end) TSKACompStatusd,
         max(case when ct.ComplianceTypeID=11 then cd.VersionOrder else null end) TSKAVersionOrder
  from Parts_cte p
       cross join ComplianceTypes_cte ct
       left join ComplianceData_cte cd on p.PartId=cd.PartId
                                          and ct.ComplianceTypeID=cd.ComplianceTypeID
  group by p.PartId
  order by p.PartId;

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können