SQL pivot基于两个“for”列

I want to pivot the following data based on both the ratetype and costtype columns. I can't seem to figure out how to do this, without perhaps concatenating the ratetype and costtype values together, then doing a pivot on that...

我想基于ratetype和costtype列来转动以下数据。我似乎无法弄清楚如何做到这一点，也许没有将ratetype和costtype值连接在一起，然后在那个上做一个支点......

Can it be done a better way?

可以做得更好吗？

Employee  Period  RateType  CostType  Value
--------------------------------------------
1         201701  Rate1     CostA     500
1         201701  Rate1     CostB     700
1         201701  Rate2     CostA     400
1         201701  Rate2     CostB     200

into

成

Employee  Period  Rate1CostA  Rate1CostB  Rate2CostA  Rate2CostB
-------------------------------------------------------------------
1         201701  500         700         400         200

The only way I can figure out doing it with by concatenating the two fields first, which feels ugly. Something like...

我可以通过首先连接两个字段来解决这个问题的唯一方法，这感觉很难看。就像是...

SELECT 
    Employee,
    Period,
    Rate1CostA, Rate1CostB,
    Rate2CostA, Rate2CostB
FROM 
    (SELECT
         Employee,
         Period,
         RateType + CostType as RateCostType,
         Value
     FROM
         MyTable) CostRate
PIVOT 
    (MAX(Value)
      FOR RateCostType IN (Rate1CostA, Rate1CostB, Rate2CostA, Rate2CostB)
    ) AS p

1 个解决方案

#1

Conditional aggregation is one approach:

条件聚合是一种方法：

SELECT Employee, Period,
       MAX(CASE WHEN RateType = 'Rate1' AND CostType = 'CostA'
                THEN Value
           END) Rate1CostA,
       MAX(CASE WHEN RateType = 'Rate1' AND CostType = 'CostB'
                THEN Value
           END) Rate1CostB,
       MAX(CASE WHEN RateType = 'Rate2' AND CostType = 'CostA'
                THEN Value
           END) Rate2CostA,
       MAX(CASE WHEN RateType = 'Rate2' AND CostType = 'CostB'
                THEN Value
           END) Rate2CostB
FROM YourTable
GROUP BY Employee, Period