--如果hour是字符型--建立測試環境
Create Table Tab
(empid Char(7),
empname Nvarchar(10),
regdate Varchar(10),
[hour] Numeric(10, 2),
[money] Numeric(10, 2),
total Numeric(10, 2))
--插入數據
Insert Tab Select '0603024',N'张三', '2007-04-05', 15.00, 6.00, 90.00
Union All Select '0603024', N'张三', '2007-04-06', 23.00, 6.00, 138.00
Union All Select '0603024', N'张三', '2007-04-07', 21.00, 6.00, 126.00
Union All Select '0603024', N'张三', '2007-04-08', 11.00, 6.00, 66.00
Union All Select '0603024', N'张三', '2007-04-05', 6.00, 9.00, 54.00
Union All Select '0605020', N'李四', '2007-04-06', 3.00, 9.00, 27.00
Union All Select '0605020', N'李四', '2007-04-07', 8.00, 9.00, 72.00
Union All Select '0605020', N'李四', '2007-04-08', 10.00, 10.00, 100.00
Union All Select '0605093', N'王五', '2007-04-05', 24.00, 6.00, 144.00
Union All Select '0605093', N'王五', '2007-04-06', 21.00, 6.00, 126.00
Union All Select '0605093', N'王五', '2007-04-07', 12.00, 6.00, 72.00
Union All Select '0605093', N'王五', '2007-04-08', 15.00, 6.00, 90.00
GO
--建立存儲過程
Create ProceDure SP_TEST
As
Begin
Select ID = Identity(Int, 1, 1), * Into #T From Tab
Select OrderID = (Select Count(*) From #T Where empid = A.empid And ID < A.ID) , * Into #T2 From #T A
Declare @S Varchar(8000), @S2 Varchar(8000)
Select @S = N' Select empid, empname', @S2 = ''
Select @S = @S + ' , Max(Case OrderID When '+ Rtrim(OrderID) + N' % 3 Then regdate Else '''' End) As regdate' + Rtrim(OrderID)
+ ' , Max(Case OrderID When '+ Rtrim(OrderID) + N' % 3 Then [hour] Else 0.00 End) As hour' + Rtrim(OrderID)
+ ' , Max(Case OrderID When '+ Rtrim(OrderID) + N' % 3 Then [money] Else 0.00 End) As money' + Rtrim(OrderID)
+ ' , Max(Case OrderID When '+ Rtrim(OrderID) + N' % 3 Then total Else 0.00 End) As total' + Rtrim(OrderID)
From (Select 0 As OrderID Union Select 1 Union Select 2) A
Select @S = @S + ' From (Select OrderID / 3 As CountID, OrderID % 3 As OrderID, empid, empname, regdate, [hour], [money], total From #T2 ) A Group By empid, empname, CountID Order By empid, empname, CountID'
EXEC(@S) Drop Table #T, #T2
End
GO
--測試
EXEC SP_TEST
GO
--刪除測試環境
Drop Table Tab
Drop ProceDure SP_TEST
Create Table Tab
(empid Char(7),
empname Nvarchar(10),
regdate Varchar(10),
[hour] Numeric(10, 2),
[money] Numeric(10, 2),
total Numeric(10, 2))
--插入數據
Insert Tab Select '0603024',N'张三', '2007-04-05', 15.00, 6.00, 90.00
Union All Select '0603024', N'张三', '2007-04-06', 23.00, 6.00, 138.00
Union All Select '0603024', N'张三', '2007-04-07', 21.00, 6.00, 126.00
Union All Select '0603024', N'张三', '2007-04-08', 11.00, 6.00, 66.00
Union All Select '0603024', N'张三', '2007-04-05', 6.00, 9.00, 54.00
Union All Select '0605020', N'李四', '2007-04-06', 3.00, 9.00, 27.00
Union All Select '0605020', N'李四', '2007-04-07', 8.00, 9.00, 72.00
Union All Select '0605020', N'李四', '2007-04-08', 10.00, 10.00, 100.00
Union All Select '0605093', N'王五', '2007-04-05', 24.00, 6.00, 144.00
Union All Select '0605093', N'王五', '2007-04-06', 21.00, 6.00, 126.00
Union All Select '0605093', N'王五', '2007-04-07', 12.00, 6.00, 72.00
Union All Select '0605093', N'王五', '2007-04-08', 15.00, 6.00, 90.00
GO
--建立存儲過程
Create ProceDure SP_TEST
As
Begin
Select ID = Identity(Int, 1, 1), * Into #T From Tab
Select OrderID = (Select Count(*) From #T Where empid = A.empid And ID < A.ID) , * Into #T2 From #T A
Declare @S Varchar(8000), @S2 Varchar(8000)
Select @S = N' Select empid, empname', @S2 = ''
Select @S = @S + ' , Max(Case OrderID When '+ Rtrim(OrderID) + N' % 3 Then regdate Else '''' End) As regdate' + Rtrim(OrderID)
+ ' , Max(Case OrderID When '+ Rtrim(OrderID) + N' % 3 Then [hour] Else 0.00 End) As hour' + Rtrim(OrderID)
+ ' , Max(Case OrderID When '+ Rtrim(OrderID) + N' % 3 Then [money] Else 0.00 End) As money' + Rtrim(OrderID)
+ ' , Max(Case OrderID When '+ Rtrim(OrderID) + N' % 3 Then total Else 0.00 End) As total' + Rtrim(OrderID)
From (Select 0 As OrderID Union Select 1 Union Select 2) A
Select @S = @S + ' From (Select OrderID / 3 As CountID, OrderID % 3 As OrderID, empid, empname, regdate, [hour], [money], total From #T2 ) A Group By empid, empname, CountID Order By empid, empname, CountID'
EXEC(@S) Drop Table #T, #T2
End
GO
--測試
EXEC SP_TEST
GO
--刪除測試環境
Drop Table Tab
Drop ProceDure SP_TEST
Create Table Tab
(empid Char(7),
empname Nvarchar(10),
regdate DateTime,
[hour] Numeric(10, 2),
[money] Numeric(10, 2),
total Numeric(10, 2))
--插入數據
Insert Tab Select '0603024',N'张三', '2007-04-05', 15.00, 6.00, 90.00
Union All Select '0603024', N'张三', '2007-04-06', 23.00, 6.00, 138.00
Union All Select '0603024', N'张三', '2007-04-07', 21.00, 6.00, 126.00
Union All Select '0603024', N'张三', '2007-04-08', 11.00, 6.00, 66.00
Union All Select '0603024', N'张三', '2007-04-05', 6.00, 9.00, 54.00
Union All Select '0605020', N'李四', '2007-04-06', 3.00, 9.00, 27.00
Union All Select '0605020', N'李四', '2007-04-07', 8.00, 9.00, 72.00
Union All Select '0605020', N'李四', '2007-04-08', 10.00, 10.00, 100.00
Union All Select '0605093', N'王五', '2007-04-05', 24.00, 6.00, 144.00
Union All Select '0605093', N'王五', '2007-04-06', 21.00, 6.00, 126.00
Union All Select '0605093', N'王五', '2007-04-07', 12.00, 6.00, 72.00
Union All Select '0605093', N'王五', '2007-04-08', 15.00, 6.00, 90.00
GO
--建立存儲過程
Create ProceDure SP_TEST
As
Begin
Select ID = Identity(Int, 1, 1), * Into #T From Tab
Select OrderID = (Select Count(*) From #T Where empid = A.empid And ID < A.ID) , * Into #T2 From #T A
Declare @S Varchar(8000), @S2 Varchar(8000)
Select @S = N' Select empid, empname', @S2 = ''
Select @S = @S + ' , Max(Case OrderID When '+ Rtrim(OrderID) + N' % 3 Then Convert(Varchar(10), regdate, 120) Else '''' End) As regdate' + Rtrim(OrderID)
+ ' , Max(Case OrderID When '+ Rtrim(OrderID) + N' % 3 Then [hour] Else 0.00 End) As hour' + Rtrim(OrderID)
+ ' , Max(Case OrderID When '+ Rtrim(OrderID) + N' % 3 Then [money] Else 0.00 End) As money' + Rtrim(OrderID)
+ ' , Max(Case OrderID When '+ Rtrim(OrderID) + N' % 3 Then total Else 0.00 End) As total' + Rtrim(OrderID)
From (Select 0 As OrderID Union Select 1 Union Select 2) A
Select @S = @S + ' From (Select OrderID / 3 As CountID, OrderID % 3 As OrderID, empid, empname, regdate, [hour], [money], total From #T2 ) A Group By empid, empname, CountID Order By empid, empname, CountID'
EXEC(@S) Drop Table #T, #T2
End
GO
--測試
EXEC SP_TEST
GO
--刪除測試環境
Drop Table Tab
Drop ProceDure SP_TEST
Create ProceDure SP_TEST
As
Begin
Select ID = Identity(Int, 1, 1), * Into #T From Tab
Select OrderID = (Select Count(*) From #T Where empid = A.empid And ID < A.ID) , * Into #T2 From #T A
Declare @S Varchar(8000), @S2 Varchar(8000)
Select @S = N' Select empid, empname', @S2 = ''
Select @S = @S + ' , Max(Case OrderID When '+ Rtrim(OrderID) + N' % 3 Then regdate Else '''' End) As regdate' + Rtrim(OrderID)
+ ' , Max(Case OrderID When '+ Rtrim(OrderID) + N' % 3 Then [hour] Else 0.00 End) As hour' + Rtrim(OrderID)
+ ' , Max(Case OrderID When '+ Rtrim(OrderID) + N' % 3 Then [money] Else 0.00 End) As money' + Rtrim(OrderID)
+ ' , Max(Case OrderID When '+ Rtrim(OrderID) + N' % 3 Then total Else 0.00 End) As total' + Rtrim(OrderID)
From (Select 0 As OrderID Union Select 1 Union Select 2) A
Select @S = @S + ' From (Select OrderID / 3 As CountID, OrderID % 3 As OrderID, empid, empname, regdate, [hour], [money], total From #T2 ) A Group By empid, empname, CountID Order By empid, empname, CountID'
EXEC(@S) Drop Table #T, #T2
End
GO
--如果regdate是DateTime型
--建立存儲過程
Create ProceDure SP_TEST
As
Begin
Select ID = Identity(Int, 1, 1), * Into #T From Tab
Select OrderID = (Select Count(*) From #T Where empid = A.empid And ID < A.ID) , * Into #T2 From #T A
Declare @S Varchar(8000), @S2 Varchar(8000)
Select @S = N' Select empid, empname', @S2 = ''
Select @S = @S + ' , Max(Case OrderID When '+ Rtrim(OrderID) + N' % 3 Then Convert(Varchar(10), regdate, 120) Else '''' End) As regdate' + Rtrim(OrderID)
+ ' , Max(Case OrderID When '+ Rtrim(OrderID) + N' % 3 Then [hour] Else 0.00 End) As hour' + Rtrim(OrderID)
+ ' , Max(Case OrderID When '+ Rtrim(OrderID) + N' % 3 Then [money] Else 0.00 End) As money' + Rtrim(OrderID)
+ ' , Max(Case OrderID When '+ Rtrim(OrderID) + N' % 3 Then total Else 0.00 End) As total' + Rtrim(OrderID)
From (Select 0 As OrderID Union Select 1 Union Select 2) A
Select @S = @S + ' From (Select OrderID / 3 As CountID, OrderID % 3 As OrderID, empid, empname, regdate, [hour], [money], total From #T2 ) A Group By empid, empname, CountID Order By empid, empname, CountID'
EXEC(@S) Drop Table #T, #T2
End
GO
/*
empid empname regdate0 hour0 money0 total0 regdate1 hour1 money1 total1 regdate2 hour2 money2 total2
0603024 张三 2007-04-05 15.00 6.00 90.00 2007-04-06 23.00 6.00 138.00 2007-04-07 21.00 6.00 126.00
0603024 张三 2007-04-08 11.00 6.00 66.00 2007-04-05 6.00 9.00 54.00 .00 .00 .00
0605020 李四 2007-04-06 3.00 9.00 27.00 2007-04-07 8.00 9.00 72.00 2007-04-08 10.00 10.00 100.00
0605093 王五 2007-04-05 24.00 6.00 144.00 2007-04-06 21.00 6.00 126.00 2007-04-07 12.00 6.00 72.00
0605093 王五 2007-04-08 15.00 6.00 90.00 .00 .00 .00 .00 .00 .00
*/