MS SQL PIVOT數據透視表

以前曾經做過練習《T-SQL PIVOT 行列轉換》https://www.cnblogs.com/insus/archive/2011/03/05/1971446.html

今天把拿出來，再練習。 以前透視列，需要手動指定。是否可以動態拿到呢？ 看看下麵的演示：

在實現之前，得先參考這篇《列值轉換為逗號分隔字元串》https://www.cnblogs.com/insus/p/10852906.html

DECLARE @pivot_cols NVARCHAR(MAX)
WITH dist_col_data AS
(
    SELECT DISTINCT [DT] FROM [dbo].[RecordHits]
)
SELECT  @pivot_cols = ISNULL(@pivot_cols + '],[', '') + CAST([DT] AS NVARCHAR(MAX)) FROM dist_col_data ORDER BY [DT];


EXECUTE ('
SELECT [RId],['+ @pivot_cols +']
FROM
(
    SELECT * FROM [dbo].[RecordHits]
) AS p
PIVOT
(
    SUM([Hits]) FOR [DT] IN (['+ @pivot_cols +'])
) AS Q;
')