如何得到按月生成的统计数据?

Question

有表如下:
id  workdate ondutyName
1   2006-1-1  a;b;
2   2006-1-2  b;c;
3   2006-1-3  a;c;
....
workdate表示值班日期,ondutyName表示值班人员,以';'隔开不同的人员,
现在要得到这样的统计结果:
比如统计2006年1月每天的值班情况,得到以下的数据:
Name 1 2 3  ....31
a    1 0 1 .......
b    1 1 0 .......
c    0 1 1 .......
如果a这天值班了,就用1表示,不值班,就用0表示
如果统计的是2006年2月数据,因为2月只有28天,那么,生成的统计表的格式就如下:
Name 1 2 3 ......28
a    .............
b    .............
c    .............
请问该怎样写这样的存储过程?

Answer 1

--建立测试数据
CREATE TABLE A(ID INT , workdate VARCHAR(10), ondutyName VARCHAR(100)) --声明表变量
INSERT A 
SELECT 1 ,  '2006-1-1', 'a;b' UNION ALL
SELECT 2 ,  '2006-1-2', 'b;c' UNION ALL 
SELECT 3 ,  '2006-1-3', 'a;c'
GO--建立辅助临时表1
SELECT TOP 8000 id = identity(int,1,1) 
INTO #1 FROM syscolumns a, syscolumns b   --建立辅助临时表2
SELECT 
    A.ID, A.workdate,
    ondutyName = SUBSTRING(A.ondutyName, B.ID, CHARINDEX(';', A.ondutyName + ';', B.ID) - B.ID) 
INTO #2
FROM A , #1 B
WHERE SUBSTRING(';' + a.ondutyName, B.id, 1) = ';'
GOSELECT * FROM #2SELECT 
ondutyName
,[1]=0 ,[2]=0 ,[3]=0 ,[4]=0 ,[5]=0 ,[6]=0 ,[7]=0 ,[8]=0 ,[9]=0 ,[10]=0 ,[11]=0 ,[12]=0 ,[13]=0 , [14]=0,[15]=0
,[16]=0 ,[17]=0 ,[18]=0 ,[19]=0 ,[20]=0 ,[21]=0 ,[22]=0 ,[23]=0 ,[24]=0 ,[25]=0 ,[26]=0 ,[27]=0 ,[28]=0 , [29]=0,[30]=0,[31]=0
INTO #3
FROM 
(SELECT DISTINCT ondutyName FROM #2) XUPDATE #3 SET [1]=1 FROM #2 a,#3 b WHERE a.ondutyName=b.ondutyName AND DAY(a.workdate)=1 
UPDATE #3 SET [2]=1 FROM #2 a,#3 b WHERE a.ondutyName=b.ondutyName AND DAY(a.workdate)=2 
UPDATE #3 SET [3]=1 FROM #2 a,#3 b WHERE a.ondutyName=b.ondutyName AND DAY(a.workdate)=3
--... SELECT * FROM #3--删除测试环境
DROP TABLE A,#1,#2,#3--查看结果
/*
ID workdate ondutyName
1 2006-1-1 a
1 2006-1-1 b
2 2006-1-2 b
2 2006-1-2 c
3 2006-1-3 a
3 2006-1-3 condutyName 1 2 3...
-------------------------------------------------
a 1 0 1...
b 1 1 0...
c 0 1 1...
*/

Answer 2

--建立测试数据
CREATE TABLE A(ID INT , workdate VARCHAR(10), ondutyName VARCHAR(100)) --声明表变量
INSERT A 
SELECT 1 ,  '2006-1-1', 'a;b' UNION ALL
SELECT 2 ,  '2006-1-2', 'b;c' UNION ALL 
SELECT 3 ,  '2006-1-3', 'a;c'
GO--建立辅助临时表1
SELECT TOP 8000 id = identity(int,1,1) 
INTO Tmp1 FROM syscolumns a, syscolumns b   --建立辅助临时表2
SELECT 
    A.ID, A.workdate,
    ondutyName = SUBSTRING(A.ondutyName, B.ID, CHARINDEX(';', A.ondutyName + ';', B.ID) - B.ID) 
INTO Tmp2
FROM A , Tmp1 B
WHERE SUBSTRING(';' + a.ondutyName, B.id, 1) = ';'
GOSELECT * FROM Tmp2DECLARE @SQL VARCHAR(8000)
DECLARE @I INT
SET @SQL='SELECT ondutyName'
SET @I=1
WHILE @I<=31
    BEGIN
        SET @SQL=@SQL+',['+CAST(@I AS VARCHAR(10))+']=0'
        SET @I=@I+1
    END
SET @SQL=@SQL+' INTO Tmp3 FROM (SELECT DISTINCT ondutyName FROM Tmp2) X'
EXEC(@SQL)SET @SQL=''SET @I=1
WHILE @I<=31
    BEGIN
        SET @SQL=@SQL+'UPDATE Tmp3 SET ['+CAST(@I AS VARCHAR(10))+']=1 FROM Tmp2 a,Tmp3 b WHERE a.ondutyName=b.ondutyName AND DAY(a.workdate)='+CAST(@I AS VARCHAR(10))+CHAR(10)
        SET @I=@I+1
    END
EXEC(@SQL)SELECT * FROM Tmp3--删除测试环境
DROP TABLE A,Tmp1,Tmp2,Tmp3--查看结果
/*
ID workdate ondutyName
1 2006-1-1 a
1 2006-1-1 b
2 2006-1-2 b
2 2006-1-2 c
3 2006-1-3 a
3 2006-1-3 condutyName 1 2 3...
-----------------------------------
a 1 0 1...
b 1 1 0...
c 0 1 1...
*/

Answer 3

--建立测试数据
CREATE TABLE A(ID INT , workdate VARCHAR(10), ondutyName VARCHAR(100)) --声明表变量
INSERT A 
SELECT 1 ,  '2006-1-1', 'a;b' UNION ALL
SELECT 2 ,  '2006-1-2', 'b;c' UNION ALL 
SELECT 3 ,  '2006-1-3', 'a;c'
GO--建立辅助临时表1
SELECT TOP 8000 id = identity(int,1,1) 
INTO Tmp1 FROM syscolumns a, syscolumns b   --建立辅助临时表2
SELECT 
    A.ID, A.workdate,
    ondutyName = SUBSTRING(A.ondutyName, B.ID, CHARINDEX(';', A.ondutyName + ';', B.ID) - B.ID) 
INTO Tmp2
FROM A , Tmp1 B
WHERE SUBSTRING(';' + a.ondutyName, B.id, 1) = ';'
GOSELECT * FROM Tmp2
GO--建立存储过程(参数:年、月)
CREATE PROC Report_Details
@YEAR INT,
@MONTH INT
AS
DECLARE @SQL VARCHAR(8000)
DECLARE @DayOfMonth INT
DECLARE @I INT
SET @DayOfMonth=DATEDIFF(day,cast(@YEAR as varchar)+'-'+cast(@MONTH as varchar)+'-01',cast(@YEAR as varchar)+'-'+cast(@MONTH+1 as varchar)+'-01') 
SET @SQL='SELECT ondutyName'
SET @I=1
WHILE @I<=@DayOfMonth
    BEGIN
        SET @SQL=@SQL+',['+CAST(@I AS VARCHAR(10))+']=0'
        SET @I=@I+1
    END
SET @SQL=@SQL+' INTO Tmp3 FROM (SELECT DISTINCT ondutyName FROM Tmp2) X'
EXEC(@SQL)SET @SQL=''SET @I=1
WHILE @I<=@DayOfMonth
    BEGIN
        SET @SQL=@SQL+'UPDATE Tmp3 SET ['+CAST(@I AS VARCHAR(10))+']=1 FROM Tmp2 a,Tmp3 b WHERE a.ondutyName=b.ondutyName AND DAY(a.workdate)='+CAST(@I AS VARCHAR(10))+CHAR(10)
        SET @I=@I+1
    END
EXEC(@SQL)SELECT * FROM Tmp3
GO--调用过程
EXEC Report_Details '2006', '1'--删除测试环境
DROP TABLE A,Tmp1,Tmp2,Tmp3
DROP PROC Report_Details--查看结果
/*
ID workdate ondutyName
1 2006-1-1 a
1 2006-1-1 b
2 2006-1-2 b
2 2006-1-2 c
3 2006-1-3 a
3 2006-1-3 condutyName 1 2 3...
-----------------------------------
a 1 0 1...
b 1 1 0...
c 0 1 1...
*/

Answer 4

用用我的~~动态sql语句CREATE TABLE AAA(ID INT , workdate VARCHAR(10), ondutyName VARCHAR(100)) --声明表变量
INSERT AAA 
SELECT 1 ,  '2006-1-1', 'a;b' UNION ALL
SELECT 2 ,  '2006-1-2', 'b;c' UNION ALL 
SELECT 3 ,  '2006-1-3', 'a;c'
go
select top 3000 identity(int,1,1)[id] into # from sysobjects a, sysobjects b
go
select substring(ondutyname+';',b.id,CHARINDEX(';',ondutyname+';',b.id)-b.id)Name,
day(workdate)workdate
into #t
from (select * from aaa where workdate BETWEEN '2006-1-1' and '2006-1-31') a,# b 
where substring(';'+ondutyname,b.id,1)=';'
godeclare @table varchar(1000)
set @table='select Name,'
select @table=@table+'(case when((select workdate from #t b where b.name=a.name and b.workdate='+
QUOTENAME(workdate,'''')+'))is null then 0 else 1 end) as '+QUOTENAME(workdate)+','
from #t
group by workdate
set @table = left(@table,len(@table)-1)+' from #t a'
exec (@table)drop table #t
drop table #

Answer 5

注:你的数据多的话~请把 declare @table varchar(1000)
改为:declare @table varchar(5000)