求SQL语句,数据显示为三列,多于三列则另起一行解决方案
求SQL语句,数据显示为三列,多于三列则另起一行
求SQL语句,数据显示为三列,多于三列则另起一行
表Tab结构及数据:
empid empname regdate hour money total
---------------------------
0603024 张三 2007-04-05 15.00 6.00 90.00
0603024 张三 2007-04-06 23.00 6.00 138.00
0603024 张三 2007-04-07 21.00 6.00 126.00
0603024 张三 2007-04-08 11.00 6.00 66.00
0605020 张三 2007-04-05 6.00 9.00 54.00
0605020 李四 2007-04-06 3.00 9.00 27.00
0605020 李四 2007-04-07 8.00 9.00 72.00
0605020 李四 2007-04-08 10.00 10.00 100.00
0605093 王五 2007-04-05 24.00 6.00 144.00
0605093 王五 2007-04-06 21.00 6.00 126.00
0605093 王五 2007-04-07 12.00 6.00 72.00
0605093 王五 2007-04-08 15.00 6.00 90.00
要求用SQL语句查询得到下表格式:
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
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
说明:将regdate,hour,money,total复制两次作为列,数据显示为三列,多于三列则另起一行
如果排列不清楚,把上面数据Copy到记事本最大化显示应该能看到
------解决方案--------------------
有些不明白
------解决方案--------------------
难,想到办法再过来
------解决方案--------------------
--如果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)
求SQL语句,数据显示为三列,多于三列则另起一行
表Tab结构及数据:
empid empname regdate hour money total
---------------------------
0603024 张三 2007-04-05 15.00 6.00 90.00
0603024 张三 2007-04-06 23.00 6.00 138.00
0603024 张三 2007-04-07 21.00 6.00 126.00
0603024 张三 2007-04-08 11.00 6.00 66.00
0605020 张三 2007-04-05 6.00 9.00 54.00
0605020 李四 2007-04-06 3.00 9.00 27.00
0605020 李四 2007-04-07 8.00 9.00 72.00
0605020 李四 2007-04-08 10.00 10.00 100.00
0605093 王五 2007-04-05 24.00 6.00 144.00
0605093 王五 2007-04-06 21.00 6.00 126.00
0605093 王五 2007-04-07 12.00 6.00 72.00
0605093 王五 2007-04-08 15.00 6.00 90.00
要求用SQL语句查询得到下表格式:
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
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
说明:将regdate,hour,money,total复制两次作为列,数据显示为三列,多于三列则另起一行
如果排列不清楚,把上面数据Copy到记事本最大化显示应该能看到
------解决方案--------------------
有些不明白
------解决方案--------------------
难,想到办法再过来
------解决方案--------------------
--如果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)