Question

Source: https://stackoverflow.com/questions/70514050/how-to-get-data-horizontally-from-table-compliance-data-based-on-part-id-and-com

I work on SQL Server 2014, and need to get data from compliance data table horizontally:

· Based on part id and compliance type Id 1,2,11;

· 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 does not have 3 compliances, then it will take Null on empty compliance Type;

· As part id 749120,4620

How to do that please?

Sample data is as follows:

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

Desired result is:

PartId ReachCompStatus ReachComplianceType ReachComplianceTypeID ReachVersionOrder RohsCompStatus RohsComplianceType RohsComplianceTypeID RohsVersionOrder TSKACompStatus TSKAComplianceType TSKAComplianceTypeID TSKAVersionOrder

2306 NotCompliant Reach 2 25 Compliant Rohs 1 3 Compliant TSKA 11 1

4620 Compliant Reach 2 25 NotCompliant Rohs 1 3

5090 Compliant Reach 2 25 NotCompliant Rohs 1 40 NotCompliant TSKA 11 1

749120 Compliant Reach 2 25

Answer

You can remove duplicates by PartId and ComplianceTypeID (and get record with the largest VersionOrder), group records by ComplianceType, and in each group transpose columns to rows and add ComplianceType to each Item, and then concatenate all records and transpose rows to columns. It is really a hassle to express the algorithm in SQL. No matter you use Cross Apply ( values … and perform a transposition, or use group by, the SQL statement will be lengthy and can only apply the current scenario. An alternative is to export data out of database and handle it in Python or SPL. SPL, the open-source Java package, is easier to be integrated into a Java program and generate much simpler code. It does the task with only two lines of code:

	A
1	=MSSQL.query("select * from ComplianceData order by 2,1,4   desc")
2	=A1.group@o1(#1,#2).group@o(#5).(~.pivot@r(PartId;Item,Value)).run(type=~.select@1(Item=="ComplianceType").Value,~.(Item=type/Item)).conj().pivot(PartId;Item,Value)

 

View SPL source code.