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
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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; ')