求SQL语句,数据显示为三列,多于三列则另起一行
求SQL语句,数据显示为三列,多于三列则另起一行

表Tab结构及数据:
empidempnameregdatehourmoneytotal
---------------------------
0603024张三2007-04-0515.006.0090.00
0603024张三2007-04-0623.006.00138.00
0603024张三2007-04-0721.006.00126.00
0603024张三2007-04-0811.006.0066.00
0605020张三2007-04-056.009.0054.00
0605020李四2007-04-063.009.0027.00
0605020李四2007-04-078.009.0072.00
0605020李四2007-04-0810.0010.00100.00
0605093王五2007-04-0524.006.00144.00
0605093王五2007-04-0621.006.00126.00
0605093王五2007-04-0712.006.0072.00
0605093王五2007-04-0815.006.0090.00


要求用SQL语句查询得到下表格式:
empidempnameregdate0hour0money0total0regdate1hour1money1total1regdate2hour2money2total2
---------------------------------------------------------------
0603024张三2007-04-0515.006.0090.002007-04-0623.006.00138.002007-04-0721.006.00126.00
0603024张三2007-04-0811.006.0066.002007-04-056.009.0054.00
0605020李四2007-04-063.009.0027.002007-04-078.009.0072.002007-04-0810.0010.00100.00
0605093王五2007-04-0524.006.00144.002007-04-0621.006.00126.002007-04-0712.006.0072.00
0605093王五2007-04-0815.006.0090.00

说明:将regdate,hour,money,total复制两次作为列,数据显示为三列,多于三列则另起一行

如果排列不清楚,把上面数据Copy到记事本最大化显示应该能看到

[解决办法]
有些不明白
[解决办法]
难,想到办法再过来
[解决办法]
--如果hour是字符型

--建立境
Create Table Tab
(empidChar(7),
empnameNvarchar(10),
regdateVarchar(10),
[hour]Numeric(10, 2),
[money]Numeric(10, 2),
totalNumeric(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