现在有下面一个表RIV_NAME Username RIV_NUM RIV2_EXAMINE RIV2_DATE
A 张山 71 杨行镇 2007-3-12 11:10:37
A 张山 83 杨行镇 2007-3-12 13:57:28
A 张山 77 杨行镇 2007-3-12 13:58:08
B 李四 91 杨行镇 2007-2-23 13:52:14
B 李四 80 月浦镇 2007-3-23 14:41:35
B 张山 73 杨行镇 2007-3-23 15:05:05
C 王五 90 杨行镇 2007-3-27 13:13:11
C 王五 94 杨行镇 2007-4-27 13:19:12
C 王五 92 杨行镇 2007-3-27 13:19:36
C 王五 95 杨行镇 2007-4-27 13:23:16 我想显示成
RIV_NAME RIV_NUM1 RIV_NUM2 RIV_NUM3 RIV2_EXAMINE RIV2_DATE
A 71 83 77 杨行镇 3月
B 91 80 73 杨行镇 3月
C 90 92 空 杨行镇 3月请大家帮忙.我想不出怎么写.谢谢了
A 张山 71 杨行镇 2007-3-12 11:10:37
A 张山 83 杨行镇 2007-3-12 13:57:28
A 张山 77 杨行镇 2007-3-12 13:58:08
B 李四 91 杨行镇 2007-2-23 13:52:14
B 李四 80 月浦镇 2007-3-23 14:41:35
B 张山 73 杨行镇 2007-3-23 15:05:05
C 王五 90 杨行镇 2007-3-27 13:13:11
C 王五 94 杨行镇 2007-4-27 13:19:12
C 王五 92 杨行镇 2007-3-27 13:19:36
C 王五 95 杨行镇 2007-4-27 13:23:16 我想显示成
RIV_NAME RIV_NUM1 RIV_NUM2 RIV_NUM3 RIV2_EXAMINE RIV2_DATE
A 71 83 77 杨行镇 3月
B 91 80 73 杨行镇 3月
C 90 92 空 杨行镇 3月请大家帮忙.我想不出怎么写.谢谢了
RIV_NAME1 ,RIV_NAME2,RIV_NAME3 这3 个字段都是表中没有的。需要在显示的时候做为datafield使用。谢谢大家了!
(RIV_NAME Varchar(10),
Username Nvarchar(10),
RIV_NUM Int,
RIV2_EXAMINE Nvarchar(10),
RIV2_DATE DateTime)
Insert 表 Select 'A', N'张山', 71, N'杨行镇', '2007-3-12 11:10:37'
Union All Select 'A', N'张山', 83, N'杨行镇', '2007-3-12 13:57:28'
Union All Select 'A', N'张山', 77, N'杨行镇', '2007-3-12 13:58:08'
Union All Select 'B', N'李四', 91, N'杨行镇', '2007-3-23 13:52:14'
Union All Select 'B', N'李四', 80, N'月浦镇', '2007-3-23 14:41:35'
Union All Select 'B', N'张山', 73, N'杨行镇', '2007-3-23 15:05:05'
Union All Select 'C', N'王五', 90, N'杨行镇', '2007-3-27 13:13:11'
Union All Select 'C', N'王五', 94, N'杨行镇', '2007-4-27 13:19:12'
Union All Select 'C', N'王五', 92, N'杨行镇', '2007-3-27 13:19:36'
Union All Select 'C', N'王五', 95, N'杨行镇', '2007-4-27 13:23:16'
GO
Declare @S Varchar(8000)
Select @S = 'Select RIV_NAME'
Select @S = @S + ', Max(Case ID When ' + Rtrim(ID) + ' Then Rtrim(RIV_NUM) Else '''' End) As [RIV_NUM' + Rtrim(ID) + ']' From
(Select (Select Count(*) From 表 Where RIV_NAME = A.RIV_NAME And RIV2_DATE <= A.RIV2_DATE And Month(RIV2_DATE) = 3) As ID, * From 表 A Where Month(RIV2_DATE) = 3) A Group By ID
Select @S = @S+ ' , Max(RIV2_EXAMINE) As RIV2_EXAMINE, DateName(mm, RIV2_DATE) As RIV2_DATE From (Select (Select Count(*) From 表 Where RIV_NAME = A.RIV_NAME And RIV2_DATE <= A.RIV2_DATE And Month(RIV2_DATE) = 3) As ID, * From 表 A Where Month(RIV2_DATE) = 3) A Where Month(RIV2_DATE) = 3 Group By RIV_NAME, DateName(mm, RIV2_DATE)'
EXEC(@S)
GO
Drop Table 表
--Result
/*
RIV_NAME RIV_NUM1 RIV_NUM2 RIV_NUM3 RIV2_EXAMINE RIV2_DATE
A 71 83 77 杨行镇 三月
B 91 80 73 杨行镇 三月
C 90 92 杨行镇 三月
*/
RIV_NAME,
Max(Case ID When 1 Then Rtrim(RIV_NUM) Else '' End) As [RIV_NUM1],
Max(Case ID When 2 Then Rtrim(RIV_NUM) Else '' End) As [RIV_NUM2],
Max(Case ID When 3 Then Rtrim(RIV_NUM) Else '' End) As [RIV_NUM3] ,
Max(RIV2_EXAMINE) As RIV2_EXAMINE,
DateName(mm, RIV2_DATE) As RIV2_DATE
From
(Select
(Select Count(*) From 表 Where RIV_NAME = A.RIV_NAME And RIV2_DATE <= A.RIV2_DATE And Month(RIV2_DATE) = 3) As ID,
*
From
表 A
Where
Month(RIV2_DATE) = 3
) A
Where
Month(RIV2_DATE) = 3
Group By
RIV_NAME, DateName(mm, RIV2_DATE)