请阅读我对这个问题的评论。



您需要动态数据库，它可以生成一个月内多少天的列数：

Sql Server中的动态PIVOT [ ^ ]

在sql server 2005 / 中使用动态列进行支点[ ^ ]

在SQL Server中创建动态PIVOT查询的脚本 [ ^ ]



使用本网站右上角的SearchBox查找更多示例。

创建过程spMonthScanReport（@Minth int，@ Year int）

as

选择EMP_ID，[1]，[2]，[3]，[4]，[5]，[6]，[7]，[8 ]，[9]，[10]，[11]，[12]，[13]，[14]，[15]，[16]，[17]，[18]，[19]，[20]，来自
$ b的[21]，[22]，[23]，[24]，[25]，[26]，[27]，[28]，[29]，[30]，[31] $ b（

选择EMP_ID，Day（SCANNEDTIME）AS DAY1，SCANNEDTIME来自tblEmployeeScan，其中MONTH（SCANNEDTIME）= @Minth和YEAR（SCANNEDTIME）= @ Years

）AA在（[1]，[2]，[3]，[4]，[5]，[6]中，DAY1的
（

MAX（SCANNEDTIME）， [7]，[8]，[9]，[10]，[11]，[12]，[13]，[14]，[15]，[16]，[17]，[18]，[19] ]，[20]，[21]， [22]，[23]，[24]，[25]，[26]，[27]，[28]，[29]，[30]，[31]）

）BB

My table tblEmployeeScan(EMPLOYEE_ID varchar(7), ScannedTime)
Everyday each employee scans 2 times, 1 time or 0 time
Now I want to crate store procedure

Create proc spMonthScanReport
@MONTH int,
@YEAR int

which returns a mothly report like below

EMPLOYEE    	  ID  	1		   	2		3	4.......	31  (All days in month)
0000001           SCN1	1/1/2014 06:00:00		2/1/2014 06:10:00
0000001           SCN2	1/1/2014 15:00:00		2/1/2014 14:00:00
0000002           SCN1 	1/1/2014 07:00:00		2/1/2014 06:10:00
0000002           SCN2	1/1/2014 15:00:00		2/1/2014 14:00:00
0000003           SCN1 	 NULL			        2/1/2014 06:10:00
0000003           SCN2 	1/1/2014 15:00:00		NULL

Please support me

Please, read my comment to the question.

You need dynamic pivot which generates as many column as many days is in a month:
Dynamic PIVOT in Sql Server[^]
pivots with dynamic columns in sql server 2005/[^]
Script to create dynamic PIVOT queries in SQL Server[^]

Use SearchBox on the right-top corner of this site to find more examples.

Create Procedure spMonthScanReport (@Minth int, @Years int)
as
select EMP_ID, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31] from
(
select EMP_ID, Day(SCANNEDTIME) AS DAY1, SCANNEDTIME from tblEmployeeScan where MONTH(SCANNEDTIME)=@Minth and YEAR(SCANNEDTIME)=@Years
) AA pivot
(
MAX(SCANNEDTIME) for DAY1 in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) BB