存储过程中传入月份（如2019-06），遍历一个月份中的每一天 ,并列转行

[灌木子]

存储过程中传入月份（如2019-06），遍历一个月份中的每一天 ,并列转行

[文盲老顾]

declare @dt date,@first date,@days int,@sql nvarchar(max)

set @dt = '2019-6-8'

select @first = dateadd(d,-datepart(d,@dt)+1,@dt),@days=31-datepart(d,dateadd(d,31,@first))

select @sql = 'select * from (select dateadd(d,number,@first) as dt,number+1 as dayNumber from master..spt_values where type=''p'' and number<=@days) a pivot(max(dt) for dayNumber in ('
	+ stuff((
		select ',['+convert(varchar,number+1)+']' from master..spt_values where type='p' and number<=@days for xml path('')
	),1,1,'')+')) p'

exec sp_executesql @sql,N'@first date,@days int',@first=@first,@days=@days