sql 行转列_SQL 动态行转列

根据每月工作日统计每人每天KPI，最终希望报表呈现效果如下：

测试数据，统计5月1日-5月31日

SQL脚本：

DECLARE @sql VARCHAR(8000)

set @sql = 'SELECT [Name],'

SELECT @sql = @sql+'SUM(CASE [day] WHEN '''+day+'''THEN [amount] ELSE 0 END) AS '''+QUOTENAME(RTrim(DAY))+''','

FROM (SELECT DISTINCT top 31 day FROM [TableName] where [AddDate] between '2019-05-01 00:00.000' and '2019-05-31 23:59.000' order by [day]) as a

SELECT @sql = LEFT(@sql,LEN(@sql)-1) + 'FROM [TableName] where [AddDate] between ''2019-05-01 00:00.000'' and ''2019-05-31 23:59.000'' GROUP BY [Name] '

PRINT(@sql)

EXEC(@sql)