表记录:
id ,LoNo ,num
1,1111,1
2,1111,2
3,1111,3
4,1111,4
5,1111,1
6,1111,2
7,1111,3现在我想得到的是
Lono num1,num2,num3,num4,num5,num6,num7
1111,1,2,3,4,1,2,3不知道怎样转置,
num1,num2,num3,num4,num5,num6,num7数据顺序不限,
id ,LoNo ,num
1,1111,1
2,1111,2
3,1111,3
4,1111,4
5,1111,1
6,1111,2
7,1111,3现在我想得到的是
Lono num1,num2,num3,num4,num5,num6,num7
1111,1,2,3,4,1,2,3不知道怎样转置,
num1,num2,num3,num4,num5,num6,num7数据顺序不限,
(id Int,
LoNo Char(4),
num Int)
Insert TEST Select 1,1111,1
Union All Select 2,1111,2
Union All Select 3,1111,3
Union All Select 4,1111,4
Union All Select 5,1111,1
Union All Select 6,1111,2
Union All Select 7,1111,3
GO
--如果相同數據的LoNo的紀錄條數固定為7個
Select
LoNo,
SUM(Case id When 1 Then num Else 0 End) As num1,
SUM(Case id When 2 Then num Else 0 End) As num2,
SUM(Case id When 3 Then num Else 0 End) As num3,
SUM(Case id When 4 Then num Else 0 End) As num4,
SUM(Case id When 5 Then num Else 0 End) As num5,
SUM(Case id When 6 Then num Else 0 End) As num6,
SUM(Case id When 7 Then num Else 0 End) As num7
From
TEST
Group By
LoNo--如果相同數據的LoNo的紀錄條數不是固定為7個
Declare @S Varchar(8000)
Select @S = ' Select LoNo'
Select @S = @S + ', SUM(Case id When ' + Rtrim(id) + ' Then num Else 0 End) As num' + Rtrim(id)
From TEST Group By id
Select @S = @S + ' From TEST Group By LoNo'
EXEC(@S)
GO
Drop Table TEST
--Result
/*
LoNo num1 num2 num3 num4 num5 num6 num7
1111 1 2 3 4 1 2 3
*/
表记录:
id ,LoNo ,num
1,1111,1
2,1111,2
3,1111,3
4,1111,4
5,1111,1
6,1111,2
7,1111,3
中id没有实际意义,自增长的。
id ,LoNo ,num
1,1111,1
2,1111,2
3,1111,3
4,1111,4
5,1111,1
6,1111,2
7,1111,3
8,1112,1
9,1112,2
10,1112,3
11,1112,4
12,1112,1
13,1112,2
14,1112,3
象这样的记录转置
刚没有仔细看,楼上应该是正确的,并没有借助id来区分。
----------
實際上用了這個id,當時只有這麼幾條數據,所以理解的過於簡單了, 馬上修改下。
(id Int,
LoNo Char(4),
num Int)
Insert TEST Select 1,1111,1
Union All Select 2,1111,2
Union All Select 3,1111,3
Union All Select 4,1111,4
Union All Select 5,1111,1
Union All Select 6,1111,2
Union All Select 7,1111,3
Union All Select 8,1112,1
Union All Select 9,1112,2
Union All Select 10,1112,3
Union All Select 11,1112,4
Union All Select 12,1112,1
Union All Select 13,1112,2
Union All Select 14,1112,3GO
--如果相同數據的LoNo的紀錄條數固定為7個
Select
LoNo,
SUM(Case Countid When 1 Then num Else 0 End) As num1,
SUM(Case Countid When 2 Then num Else 0 End) As num2,
SUM(Case Countid When 3 Then num Else 0 End) As num3,
SUM(Case Countid When 4 Then num Else 0 End) As num4,
SUM(Case Countid When 5 Then num Else 0 End) As num5,
SUM(Case Countid When 6 Then num Else 0 End) As num6,
SUM(Case Countid When 7 Then num Else 0 End) As num7
From
(Select *, (Select Count(*) From TEST Where LoNo = A.LoNo And id <= A.id) As Countid From TEST A) B
Group By
LoNo--如果相同數據的LoNo的紀錄條數不是固定為7個
Declare @S Varchar(8000)
Select @S = ' Select LoNo'
Select @S = @S + ', SUM(Case Countid When ' + Rtrim(Countid) + ' Then num Else 0 End) As num' + Rtrim(Countid)
From (Select *, (Select Count(*) From TEST Where LoNo = A.LoNo And id <= A.id) As Countid From TEST A) B Group By Countid
Select @S = @S + ' From (Select *, (Select Count(*) From TEST Where LoNo = A.LoNo And id <= A.id) As Countid From TEST A) B Group By LoNo'
EXEC(@S)
GO
Drop Table TEST
--Result
/*
LoNo num1 num2 num3 num4 num5 num6 num7
1111 1 2 3 4 1 2 3
1112 1 2 3 4 1 2 3
*/
drop table tb
gocreate table tb(id int,LoNo varchar(10),num int)
insert into tb(id ,LoNo ,num) values(1,'1111',1)
insert into tb(id ,LoNo ,num) values(2,'1111',2)
insert into tb(id ,LoNo ,num) values(3,'1111',3)
insert into tb(id ,LoNo ,num) values(4,'1111',4)
insert into tb(id ,LoNo ,num) values(5,'1111',1)
insert into tb(id ,LoNo ,num) values(6,'1111',2)
insert into tb(id ,LoNo ,num) values(7,'1111',3)
insert into tb(id ,LoNo ,num) values(1,'1112',1)
insert into tb(id ,LoNo ,num) values(2,'1112',2)
insert into tb(id ,LoNo ,num) values(3,'1112',3)
insert into tb(id ,LoNo ,num) values(4,'1112',4)
insert into tb(id ,LoNo ,num) values(5,'1112',1)
insert into tb(id ,LoNo ,num) values(6,'1112',2)
insert into tb(id ,LoNo ,num) values(7,'1112',3)
goif object_id('pubs..f_hb') is not null
drop function f_hb
go--创建一个合并的函数
create function f_hb(@LoNo varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(num as varchar) from tb where LoNo = @LoNo
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果:
select lono + ',' + num as lono from
(
select distinct LoNo , dbo.f_hb(LoNo) as num from tb
) tdrop table tb/*
lono
------------------
1111,1,2,3,4,1,2,3
1112,1,2,3,4,1,2,3(所影响的行数为 2 行)*/
countid确实是看错了,没有仔细看,呵呵,只是个生成排序序号而已