问题

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 I 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 not have 3 compliance then it will take Null on empty compliance Type

· as part id 749120,4620

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

Result required as

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

解答

先按PartId、ComplianceTypeID去重（取VersionOrder值最大的记录），再按ComplianceType分组，每组内列转行并且用每组的ComplianceType拼接在每个Item前，合并后再行转列即可。这类运算用SQL写起来很麻烦，无论是使用Cross Apply ( values … 再转置或者用group by，写出的SQL冗长且不通用。通常的办法是读出来用Python或SPL来做， SPL（一种 Java 的开源包）更容易被Java应用集成，代码也更简单一点，只要两句：

SPL源代码：https://github.com/SPLWare/esProc

问答搜集