SQL 行列转换

数据列转换成行。

其中一列需要转换成行，因为列的值不确定所以用动态执行SQL 

@sql对象拼写出要转换行的列的值。

 

DECLARE @sql VARCHAR(1000)
SELECT @sql = ISNULL(@sql + ',', '') + [ADVERTISE_NAME] FROM [TBL_ADVERTISE_MASTER]
SELECT @sql
EXEC('
    SELECT [DATE]+RIGHT([DAY],2) AS DATE
        ,' + @sql + '
    FROM
    (
        SELECT [DATE]
        ,[DAY]
        ,[ADVERTISE_NAME]
        ,[PRICE]
        FROM
        (
            SELECT * FROM [TBL_ADVERTISE_PRICE_DETAIL] AS TBL_01 WITH(NOLOCK)
            LEFT JOIN [TBL_ADVERTISE_MASTER] AS TBL_02 WITH(NOLOCK)
            ON TBL_01.[ADV_CODE] = TBL_02.[CODE]
   
        ) AS TBL
        UNPIVOT ([PRICE] FOR [DAY] IN ([PRICE_01],[PRICE_02],[PRICE_03],[PRICE_04],[PRICE_05],[PRICE_06],[PRICE_07],[PRICE_08],[PRICE_09],[PRICE_10]
            ,[PRICE_11],[PRICE_12],[PRICE_13],[PRICE_14],[PRICE_15],[PRICE_16],[PRICE_17],[PRICE_18],[PRICE_19],[PRICE_20]
            ,[PRICE_21],[PRICE_22],[PRICE_23],[PRICE_24],[PRICE_25],[PRICE_26],[PRICE_27],[PRICE_28],[PRICE_29],[PRICE_30],[PRICE_31]) -- 列转换成行
        ) AS UNPVT
    ) AS T
    PIVOT (MAX([PRICE]) FOR T.[ADVERTISE_NAME] IN (' + @sql + ')) AS T2 -- 行转换成列
 ORDER BY [DAY]
')