MS SQL PIVOT数据透视表,T-SQL PIVOT 行列转换,列值转换为逗号分隔字符串

佚名 6年前 (2019-05-16) 随笔 1733人围观抢沙发百度已收录

以前曾经做过练习《T-SQL PIVOT 行列转换》https://www.cnblogs.com/insus/archive/2011/03/05/1971446.html

今天把拿出来，再练习。以前透视列，需要手动指定。是否可以动态拿到呢？看看下面的演示：

SRE实战互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

在实现之前，得先参考这篇《列值转换为逗号分隔字符串》https://www.cnblogs.com/insus/p/10852906.html

MS SQL PIVOT数据透视表,T-SQL PIVOT 行列转换,列值转换为逗号分隔字符串随笔第2张

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;
')