数据列转换成行。
其中一列需要转换成行,因为列的值不确定所以用动态执行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]
')