我原來所用的方法是這樣的
建立一個臨時表ABCD，裡面有Cardid，以及1...31號，共32個字段
我所寫的存儲過程是這樣的，當然還有點問題
CREATE PROCEDURE SP_ABCD(@Year int,@Month int)
AS
begin
Declare @CardID varchar(16)
Declare @IODT datetime
Declare @RCount int
Declare @MDay int
Declare Cur_ABCD Cursor For
SELECT CardID, DAY(DT) AS MDay, CONVERT(VARCHAR, DT, 108) AS DTime from inout
WHERE Year(DT)=@Year and Month(DT)=@Month
GROUP BY CardID, DAY(DT),CONVERT(VARCHAR, DT, 108)
Open Cur_ABCD
FETCH NEXT FROM Cur_ABCD into @CardID,@MDay, @IODT
WHILE @@FETCH_STATUS <>-1
begin
if @MDay=1
begin
insert into ABCD(CardID,D1) Values(@CardID,@IODT)
end
else if @MDay=2
begin
insert into ABCD(CardID,D2) Values(@CardID,@IODT)
end
else if @MDay=3
begin
insert into ABCD(CardID,D3) Values(@CardID,@IODT)
end
else if @MDay=4
begin
insert into ABCD(CardID,D4) Values(@CardID,@IODT)
end
else if @MDay=5
begin
insert into ABCD(CardID,D5) Values(@CardID,@IODT)
end
else if @MDay=6
begin
insert into ABCD(CardID,D6) Values(@CardID,@IODT)
end
else if @MDay=7
begin
insert into ABCD(CardID,D7) Values(@CardID,@IODT)
end
else if @MDay=8
begin
insert into ABCD(CardID,D8) Values(@CardID,@IODT)
end
else if @MDay=9
begin
insert into ABCD(CardID,D9) Values(@CardID,@IODT)
end
else if @MDay=10
begin
insert into ABCD(CardID,D10) Values(@CardID,@IODT)
end
else if @MDay=11
begin
insert into ABCD(CardID,D11) Values(@CardID,@IODT)
end
else if @MDay=12
begin
insert into ABCD(CardID,D12) Values(@CardID,@IODT)
end
else if @MDay=13
begin
insert into ABCD(CardID,D13) Values(@CardID,@IODT)
end
else if @MDay=14
begin
insert into ABCD(CardID,D14) Values(@CardID,@IODT)
end
else if @MDay=15
begin
insert into ABCD(CardID,D15) Values(@CardID,@IODT)
end
else if @MDay=16
begin
insert into ABCD(CardID,D16) Values(@CardID,@IODT)
end
else if @MDay=17
begin
insert into ABCD(CardID,D17) Values(@CardID,@IODT)
end
else if @MDay=18
begin
insert into ABCD(CardID,D18) Values(@CardID,@IODT)
end
else if @MDay=19
begin
insert into ABCD(CardID,D19) Values(@CardID,@IODT)
end
else if @MDay=20
begin
insert into ABCD(CardID,D20) Values(@CardID,@IODT)
end
else if @MDay=21
begin
insert into ABCD(CardID,D21) Values(@CardID,@IODT)
end
else if @MDay=22
begin
insert into ABCD(CardID,D22) Values(@CardID,@IODT)
end
else if @MDay=23
begin
insert into ABCD(CardID,D23) Values(@CardID,@IODT)
end
else if @MDay=24
begin
insert into ABCD(CardID,D24) Values(@CardID,@IODT)
end
else if @MDay=25
begin
insert into ABCD(CardID,D25) Values(@CardID,@IODT)
end
else if @MDay=26
begin
insert into ABCD(CardID,D26) Values(@CardID,@IODT)
end
else if @MDay=27
begin
insert into ABCD(CardID,D27) Values(@CardID,@IODT)
end
else if @MDay=28
begin
insert into ABCD(CardID,D28) Values(@CardID,@IODT)
end
else if @MDay=29
begin
insert into ABCD(CardID,D29) Values(@CardID,@IODT)
end
else if @MDay=30
begin
insert into ABCD(CardID,D30) Values(@CardID,@IODT)
end
else if @MDay=31
begin
insert into ABCD(CardID,D31) Values(@CardID,@IODT)
end
FETCH NEXT FROM Cur_ABCD into @CardID,@MDay,@IODT
end
CLOSE Cur_ABCD
DEALLOCATE Cur_ABCD
end
GO
]