如何從基于部件ID和合規性型別ID的合規性資料表中水平獲取資料？

我在 SQL Server 2014 上作業，我需要從合規性資料表中水平獲取資料。

• 基于部件 ID 和合規型別 ID 1,2,11
• 每個零件 ID 每 3 種合規型別將有一行
• 每個零件的每一行都有 3 個合規型別 ID 1,2,11
• 如果零件沒有 3 合規性，則在空合規性型別上將采用 Null
• 作為零件 ID 749120,4620

請問怎么做？

示例資料如下

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

結果要求為

uj5u.com熱心網友回復：

似乎您想對資料進行 UNPIVOT，然后 PIVOT

例子

Select *
 From  (
        Select PartId
              ,B.*
         From #ComplianceData A
         Cross Apply ( values ( concat(ComplianceType,'ComplianceTypeID'),left(ComplianceTypeID,50))
                             ,( concat(ComplianceType,'CompStatus'),CompStatus)
                             ,( concat(ComplianceType,'VersionOrder'),left(VersionOrder,50))
                     ) B(Item,Value)
       ) src
 Pivot ( max(Value) for Item in (   [RohsComplianceTypeID],
                                    [RohsCompStatus],
                                    [RohsVersionOrder],
                                    [ReachComplianceTypeID],
                                    [ReachCompStatus],
                                    [ReachVersionOrder],
                                    [TSKAComplianceTypeID],
                                    [TSKACompStatus],
                                    [TSKAVersionOrder]
                                ) 
       ) src

uj5u.com熱心網友回復：

您可以使用 Group By

select  PartId,
        max(IIF(ComplianceType = 'Rohs', ComplianceTypeID, NULL)) as RohsComplianceTypeID,
        max(IIF(ComplianceType = 'Rohs', CompStatus, NULL)) as RohsCompStatus,
        max(IIF(ComplianceType = 'Rohs', VersionOrder, NULL)) as RohsVersionOrder,
        max(IIF(ComplianceType = 'Rohs', ComplianceType, NULL)) as RohsComplianceType,
        
        max(IIF(ComplianceType = 'Reach', ComplianceTypeID, NULL)) as ReachComplianceTypeID,
        max(IIF(ComplianceType = 'Reach', CompStatus, NULL)) as ReachCompStatus,
        max(IIF(ComplianceType = 'Reach', VersionOrder, NULL)) as ReachVersionOrder,
        max(IIF(ComplianceType = 'Reach', ComplianceType, NULL)) as ReachComplianceType,
        
        
        max(IIF(ComplianceType = 'TSKA', ComplianceTypeID, NULL)) as TSKAComplianceTypeID,
        max(IIF(ComplianceType = 'TSKA', CompStatus, NULL)) as TSKACompStatus,
        max(IIF(ComplianceType = 'TSKA', VersionOrder, NULL)) as TSKAVersionOrder,
        max(IIF(ComplianceType = 'TSKA', ComplianceType, NULL)) as TSKAComplianceType
               
from #ComplianceData 
group by PartId

db<>fiddle 中的演示

標籤：sql sql-server 查询语句 存储过程 sql-server-2014

上一篇：Angular匯出功能（excel匯出功能、檔案資料流匯出功能、圖片的下載匯出功能）

下一篇：有沒有辦法在sqlserver中將表名作為變數？[復制]