有數據表,字段有ID,AutoNumber,Result,每個AutoNumber者有5條記錄,現要把相同AutoNumber的記錄合並成如這樣的格式AutoNumber,Result1,Result2,Result3,Result4,Result5,請問用什麼語句實現?謝謝!
表的一些數據:
ID AutoNumber Result
1 5265 1TH/2006.04.20/待抽
2 5265
3 5265 2/ /不適用
4 5265
5 5265
6 5266 1TH/2006.04.20/待抽
7 5266 2th/2006.04.21/不適用
8 5266
9 5266
10 5266
11 5267 1TH/2006.04.21/不適用
12 5267 2th/2006.04.18/不適用
13 5267 3th/ /不適用
14 5267
15 5267
表的一些數據:
ID AutoNumber Result
1 5265 1TH/2006.04.20/待抽
2 5265
3 5265 2/ /不適用
4 5265
5 5265
6 5266 1TH/2006.04.20/待抽
7 5266 2th/2006.04.21/不適用
8 5266
9 5266
10 5266
11 5267 1TH/2006.04.21/不適用
12 5267 2th/2006.04.18/不適用
13 5267 3th/ /不適用
14 5267
15 5267
create table sss(ID int identity(1,1),AutoNumber int,Result varchar(100))
insert sss select 5265,'TH/2006.04.20/待抽'
union all select 5265,''
union all select 5265,'2/ /不適用'
union all select 5265,''
union all select 5265,''
union all select 5266,'1TH/2006.04.20/待抽'
union all select 5266,'2th/2006.04.21/不適用'
union all select 5266,''
union all select 5266,''
union all select 5266,''
union all select 5267,'1TH/2006.04.21/不適用'
union all select 5267,'2th/2006.04.18/不適用'
union all select 5267,'3th/ /不適用'
union all select 5267,''
union all select 5267,''
--执行查询
select AutoNumber,
result1=max(case when id%5=1 then result end),
result2=max(case when id%5=2 then result end),
result3=max(case when id%5=3 then result end),
result4=max(case when id%5=4 then result end),
result5=max(case when id%5=0 then result end)
from sss
group by AutoNumber
Create Table TEST
(ID Int,
AutoNumber Int,
Result Nvarchar(100))
--插入數據
Insert TEST Select 1, 5265, N'1TH/2006.04.20/待抽'
Union All Select 2, 5265, Null
Union All Select 3, 5265, N'2/ /不適用'
Union All Select 4, 5265, Null
Union All Select 5, 5265, Null
Union All Select 6, 5266, N'1TH/2006.04.20/待抽'
Union All Select 7, 5266, N'2th/2006.04.21/不適用'
Union All Select 8, 5266, Null
Union All Select 9, 5266, Null
Union All Select 10, 5266, Null
Union All Select 11, 5267, N'1TH/2006.04.21/不適用'
Union All Select 12, 5267, N'2th/2006.04.18/不適用'
Union All Select 13, 5267, N'3th/ /不適用'
Union All Select 14, 5267, Null
Union All Select 15, 5267, Null
GO
--建立函數
Create Function GetResult(@AutoNumber Int)
Returns Nvarchar(1000)
As
Begin
Declare @S Nvarchar(1000)
Set @S=''
Select @S=@S+','+Result from TEST Where AutoNumber=@AutoNumber And Result Is Not Null Order By ID
Return (Right(@S,Len(@S)-1))
End
GO
--測試
Select AutoNumber,dbo.GetResult(AutoNumber) As Result from TEST Group By AutoNumber Order By AutoNumber
Go
--刪除測試環境
Drop Table TEST
Drop Function GetResult
--結果
GO
/*
AutoNumber Result
5265 1TH/2006.04.20/待抽,2/ /不適用
5266 1TH/2006.04.20/待抽,2th/2006.04.21/不適用
5267 1TH/2006.04.21/不適用,2th/2006.04.18/不適用,3th/ /不適用
*/
我想把Result分為五個,這樣好樣分不了呀!
你寫的SQL可以,但有一個問題,如果ID號不是從1到5,6到10,這樣排下去的,就好象不對了,你說呢
create table sss(ID int identity(1,1),AutoNumber int,Result varchar(100))
insert sss select 5265,'TH/2006.04.20/待抽'
union all select 5265,''
union all select 5265,'2/ /不適用'
union all select 5265,''
union all select 5265,''
union all select 5266,'1TH/2006.04.20/待抽'
union all select 5266,'2th/2006.04.21/不適用'
union all select 5266,''
union all select 5266,''
union all select 5266,''
union all select 5267,'1TH/2006.04.21/不適用'
union all select 5267,'2th/2006.04.18/不適用'
union all select 5267,'3th/ /不適用'
union all select 5267,''
union all select 5267,''
--执行查询select AutoNumber,
result1=max(case when [count]%5=1 then result end),
result2=max(case when [count]%5=2 then result end),
result3=max(case when [count]%5=3 then result end),
result4=max(case when [count]%5=4 then result end),
result5=max(case when [count]%5=0 then result end)
from (
select *,[count]=(select count(1) from sss where id<=a.id and AutoNumber=a.AutoNumber) from sss a
) b
group by AutoNumber