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

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

CodePudding user response：

Seems like you want to UNPIVOT your data, and then PIVOT

Example

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

CodePudding user response：

You can use 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

Demo in db<>fiddle