求一个动态行转列的方法，要求使用master..spt_values

[lzylin]

如题，求一个动态行转列的方法，要求使用master..spt_values，要求，
SELECT 员工编号，当月的日期列（1-31） FROM (
SELECT STAFID,ENTERDATE,attend FROM HR_Attendance
) AS P
PIVOT (SUM(attend) for enterdate in
(当月的日期（1-31))
as pvt

SQL语句类似以上的，当月的日期（1-31）此处日期要求动态生成或者是SQL语句生成，例如用master..spt_values，请大侠帮忙

[Andy__Huang]

有关行列转换，请参考： http://blog.csdn.net/hdhai9451/article/details/5026933

[lzylin]

看清楚要求，我也知道百度，GOOGLE的

[唐诗三百首]

请提供enterdate字段的数据类型及其样例数据才好帮你写喔.. 以下SQL是产生当前月的所有日期.

select convert(varchar,dateadd(d,number,stuff(convert(varchar,getdate(),111),9,2,'01')),111) 'current'
 from master.dbo.spt_values 
 where type='P' and datediff(m,getdate(),dateadd(d,number,stuff(convert(varchar,getdate(),111),9,2,'01')))=0

/*
current
------------------------------
2013/07/01
2013/07/02
2013/07/03
2013/07/04
2013/07/05
2013/07/06
2013/07/07
2013/07/08
2013/07/09
2013/07/10
2013/07/11
2013/07/12
2013/07/13
2013/07/14
2013/07/15
2013/07/16
2013/07/17
2013/07/18
2013/07/19
2013/07/20
2013/07/21
2013/07/22
2013/07/23
2013/07/24
2013/07/25
2013/07/26
2013/07/27
2013/07/28
2013/07/29
2013/07/30
2013/07/31

(31 row(s) affected)
*/

[lzw_0736]

IF OBJECT_ID('tempdb..#ta') IS NOT NULL DROP TABLE #ta; IF OBJECT_ID('tempdb..#tb') IS NOT NULL DROP TABLE #tb; declare @day int,@date_b datetime,@date_e datetime select @day=32-Day(GETDATE()+32-Day(GETDATE())), @date_b=convert(datetime,convert(varchar(20), DATEPART(yy,GETDATE())*10000+100+1)) SET @date_e=DATEADD(DAY,@day-1,@date_b) SELECT STAFID,convert(CHAR(10),ENTERDATE,20) ENTERDATE,SUM(attend) attend INTO #ta FROM HR_Attendance WHERE ENTERDATE BETWEEN (@date_b) AND (@date_e) GROUP BY STAFID,convert(CHAR(10),ENTERDATE,20) select number ,convert(CHAR(10),dateadd(day,number-1,@date_b),20) Date,100 amount INTO #tb from master..spt_values sv where [type]='p' and number between 1 and (@day) declare @sql varchar(max),@sql2 varchar(max) select @sql = isnull(@sql + '],[' , '') + date from #tb set @sql = '[' + @sql + ']' select @sql2 = isnull(@sql2 + ',' , ',') + '['+RTRIM(date)+'] as ['+RTRIM(number)+']' from #tb set @sql='select STAFID'+@sql2+' from #ta a pivot (sum(attend) for ENTERDATE in (' + @sql + ')) b' EXEC(@sql)

[lzylin]

我也有做出来，只是不想用变量，希望可以通过函数或者其他方法直接得出日期列，而不是用拼凑的方法再用EXEC运行，明白我的意思吗？ ALTER PROCEDURE [dbo].[sp_RptMonth] @StartDate DATETIME, --查询开始日期 @EndDate DATETIME, --查询结束日期 @DeptID varchar(50) AS /* 查询数据 */ declare @S varchar(2000) declare @I varchar(4000) /* 生成 1-31日的sql语句 */ select @s=isnull(@s+',','')+ '['+ convert(varchar(10),dateadd(day,number-1,@StartDate),120)+']' ,@I=isnull(@I+',','')+ '['+ convert(varchar(10),number)+']=['+ convert(varchar(10),dateadd(day,number-1,@StartDate),120)+']' from master..spt_values where number>=DAY(@StartDate) and number <= DAY(@EndDate) and type='p' exec ('select v.*,w.attend,w.late,w.early,w.absence from ('+ 'select stafid,' + @I + ' from (SELECT STAFID,ENTERDATE,attend FROM HR_Attendance) as P PIVOT (SUM(attend) for enterdate in('+ @s + ')) as pvt ' + ') as V inner join ( select stafid,attend =SUM(attend),late=SUM(late),early=SUM(early),absence=SUM(absence) from HR_Attendance group by STAFID,month(EnterDate) ) as w on v.stafid = w.stafid ' )