How to get data from table Compliance Data Horizontally Based on Part Id and Compliance Type Id?

Transact-SQL
, ,
1

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')
PartId RohsComplianceTypeID RohsCompStatus RohsVersionOrder RohsComplianceType ReachComplianceTypeID ReachCompStatus ReachVersionOrder ReachComplianceType TSKAComplianceTypeID TSKACompStatus TSKAVersionOrder TSKAComplianceType
5090 1 NotCompliant 40 Rohs 2 Compliant 25 Reach 11 NotCompliant 1 TSKA
2306 1 Compliant 3 Rohs 2 NotCompliant 25 Reach 11 Compliant 1 TSKA
4620 1 NotCompliant 3 Rohs 2 Compliant 25 Reach
749120 2 Compliant 25 Reach
2

What have you tried?

3

Have you tried connecting to the same table three times?
FROM #ComplianceData A
JOIN #ComplianceData B ON ...
JOIN #ComplianceData C ON ...

4

can you show me how please

5

What have you tried? Note the join should be a left join on PartID.

1 Like
7

Trying again... I had left something out of the code and had to delete the previous post.

A lot of people will tell you to use the PIVOT operator for things like this. I'm going to tell you to use the ancient "Black Arts" method called a CROSSTAB. You can learn much more about it and how it works at the following article:

It's an incredibly powerful tool that's necessary for all sorts of reporting. Now, the code in the article uses SUM() instead of (MAX). It doesn't matter. They're both a form of an aggregate. Think of MAX() as the character based (SUM) of a single item.

Here's the code that works with the test data that you provided and thank you for providing it. I wouldn't have touched this post without both the readily consumable data and the final output example. :smiley:

Here's the code. As you can see, it's incredibly symmetrical for each 4 column group. That means that I wrote the code for 1 group of 4, pasted that, and then made the "vertical" modifications in each group. It makes writing stuff like this super easy, super fast, and super easy to check for errors. It's something that everyone should learn how to do. I simply call it the "River Format". And see the comment in the CTE of the code for how I isolated only the latest version of each PartID/ComplianceTypeID pair.

   WITH CTE AS
(--==== This numbers rows in descending VersionOrder by PartId and ComplianceTypeID.
     -- The largest VersionOrder will have a Selector value of 1 when there are "ties".
 SELECT *
        ,Selector = ROW_NUMBER() OVER (PARTITION BY PartID,ComplianceTypeID ORDER BY VersionOrder DESC)
   FROM #ComplianceData
)--===== This does the CROSSTAB with the filter of Selector = 1 to only include the latest version.
 SELECT  PartId                 = PartID

        ,RohsComplianceTypeID   = MAX(IIF(ComplianceTypeID =  1,CONVERT(VARCHAR( 5),ComplianceTypeID),''))
        ,RohsCompStatus         = MAX(IIF(ComplianceTypeID =  1,CONVERT(VARCHAR(30),CompStatus      ),'')) 
        ,RohsVersionOrder       = MAX(IIF(ComplianceTypeID =  1,CONVERT(VARCHAR( 5),VersionOrder    ),'')) 
        ,RohsComplianceType     = MAX(IIF(ComplianceTypeID =  1,CONVERT(VARCHAR(30),ComplianceType  ),''))

        ,ReachComplianceTypeID  = MAX(IIF(ComplianceTypeID =  2,CONVERT(VARCHAR( 5),ComplianceTypeID),''))
        ,ReachCompStatus        = MAX(IIF(ComplianceTypeID =  2,CONVERT(VARCHAR(30),CompStatus      ),''))
        ,ReachVersionOrder      = MAX(IIF(ComplianceTypeID =  2,CONVERT(VARCHAR( 5),VersionOrder    ),''))
        ,ReachComplianceType    = MAX(IIF(ComplianceTypeID =  2,CONVERT(VARCHAR(30),ComplianceType  ),''))

        ,TSKAComplianceTypeID   = MAX(IIF(ComplianceTypeID = 11,CONVERT(VARCHAR( 5),ComplianceTypeID),''))
        ,TSKACompStatus         = MAX(IIF(ComplianceTypeID = 11,CONVERT(VARCHAR(30),CompStatus      ),''))
        ,TSKAVersionOrder       = MAX(IIF(ComplianceTypeID = 11,CONVERT(VARCHAR( 5),VersionOrder    ),''))
        ,TSKAComplianceType     = MAX(IIF(ComplianceTypeID = 11,CONVERT(VARCHAR(30),ComplianceType  ),''))

   FROM CTE
  WHERE Selector = 1 --This is what get's the latest rows by PartID and ComplianceTypeID from the CTE
  GROUP BY PartID
  ORDER BY PartID
;

Seriously... this type of code is needed a lot. Study the article and study the code above. You should never have to ask this type of question again because it's super easy and it will make you look like a hero at work.

p.s. The CONVERTs are to convert numerics to VARCHAR() so that missing number produce blanks instead of zeros.

1 Like
8

No joins required. :wink:

9

Thanks Jeff.

10

thank you very much for help and support

11

You're welcome and thank you for the feedback. You're the one that will need to support the code so I'll ask... do you understand how the code works?

12

yes i under stand

1 Like