创建动态和通用枢轴数据

Question

我想根据TMP表中的内容以更动态的方式创建下面的枢轴数据，而无需硬编码。有什么办法吗？

该结构必须灵活，因为它可以经常更改。

意味着我想根据TMP表中的AGG字段中的内容创建列。目前，表具有三个值 - ＆gt; “ xx”，“ yy”和“ zz”，因此我需要创建三列。但是在上面的SQL中，我对三列进行了硬编码。由于TMP表也可以具有更多的值，因此必须将其更改为更具动态的实现。因此，我需要创建相应的列。我希望这是可以理解的。

WITH TMP AS
    (SELECT 'XX' AS AGG,
            50 AS VALUE,
            0 AS MONTH
     UNION ALL
     SELECT 'XX' AS AGG,
            150 AS VALUE,
            1 AS MONTH
     UNION ALL
     SELECT 'XX' AS AGG,
            300 AS VALUE,
            2 AS MONTH
     UNION ALL
     SELECT 'YY' AS AGG,
            25 AS VALUE,
            0 AS MONTH
     UNION ALL
     SELECT 'YY' AS AGG,
            50 AS VALUE,
            1 AS MONTH
     UNION ALL
     SELECT 'YY' AS AGG,
            75 AS VALUE,
            2 AS MONTH
     UNION ALL
     SELECT 'ZZ' AS AGG,
            500 AS VALUE,
            0 AS MONTH
     UNION ALL
     SELECT 'ZZ' AS AGG,
            600 AS VALUE,
            1 AS MONTH
     UNION ALL
     SELECT 'ZZ' AS AGG,
            700 AS VALUE,
            2 AS MONTH),


    SELECT Month,
           MAX(CASE Agg WHEN 'XX' THEN VALUE END) AS XX,
           MAX(CASE Agg WHEN 'YY' THEN VALUE END) AS YY,
           MAX(CASE Agg WHEN 'ZZ' THEN VALUE END) AS ZZ
    FROM TMP
    GROUP BY MONTH