动态SQL根据日期创建列

SELECT * INTO #dates FROM ( SELECT DATEADD(day, number, '2021-01-01') AS date, ABS(CAST(CAST(NEWID() AS BINARY(3)) AS INT)) AS value FROM master..spt_values WHERE type = 'P' AND number BETWEEN 0 AND 31 ) AS t select * from #dates DECLARE @columns AS NVARCHAR(MAX) DECLARE @query AS NVARCHAR(MAX) SELECT @columns = COALESCE(@columns + ', ', '') + '[' + CONVERT(VARCHAR(10), date, 120) + ']' FROM ( SELECT DISTINCT date FROM #dates ) AS dates SET @query = ' SELECT * FROM ( SELECT date, value FROM #dates ) AS t PIVOT ( SUM(value) FOR date IN (' + @columns + ') ) AS pvt' EXECUTE sp_executesql @query