select f1
,f2_1='2000-01',f3_1=max(case f2 when '2000-01' then f3 end)
,f2_2='2000-07',f3_2=max(case f2 when '2000-07' then f3 end)
,f2_3='2001-07',f3_3=max(case f2 when '2001-01' then f3 end)
from 表 group by f1
,f2_1='2000-01',f3_1=max(case f2 when '2000-01' then f3 end)
,f2_2='2000-07',f3_2=max(case f2 when '2000-07' then f3 end)
,f2_3='2001-07',f3_3=max(case f2 when '2001-01' then f3 end)
from 表 group by f1
--1.创建一个合并的函数
create function fmerg(@id char(5))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+' '+f2 + ' '+f3 from 表A where f1=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go--调用自定义函数得到结果
select distinct f1,dbo.fmerg(f1) from 表A
create table t1(f1 char(5),f2 char(7),f3 varchar(3) )
insert t1 select '00093','2000-01','O'
union all select '00093','2000-07','A+'
union all select '00093','2001-01','A+'
union all select '00092','2000-01','O'
union all select '00092','2000-07','A+'
union all select '00092','2001-01','A+'--1.创建一个合并的函数
create function fmerg(@id char(5))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+' '+f2 + ' '+f3 from t1 where f1=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go--调用自定义函数得到结果
select distinct f1,dbo.fmerg(f1) from t1f1
----- ----------------------------------
00092 2000-01 O 2000-07 A+ 2001-01 A+
00093 2000-01 O 2000-07 A+ 2001-01 A+(所影响的行数为 2 行)
我在下面这贴里写的测试通过,你可以照着改一改表名、字段名就成了:http://expert.csdn.net/Expert/topic/2568/2568757.xml?temp=.1126825
create table t1(f1 char(5),f2 char(7),f3 varchar(3) )
insert t1 select '00093','2000-01','O'
union all select '00093','2000-07','A+'
union all select '00093','2001-01','A+'
union all select '00092','2000-01','O'
union all select '00092','2000-07','A+'
union all select '00092','2001-01','A+'
go--查询处理
select f1
,f2_1='2000-01',f3_1=max(case f2 when '2000-01' then f3 end)
,f2_2='2000-07',f3_2=max(case f2 when '2000-07' then f3 end)
,f2_3='2001-07',f3_3=max(case f2 when '2001-01' then f3 end)
from t1 group by f1
go--删除测试表
drop table t1/*--测试结果
f1 f2_1 f3_1 f2_2 f3_2 f2_3 f3_3
----- ------- ---- ------- ---- ------- ----
00092 2000-01 O 2000-07 A+ 2001-07 A+
00093 2000-01 O 2000-07 A+ 2001-07 A+(所影响的行数为 2 行)
--*/
declare @s varchar(8000)
set @s=''
select @s=@s+',['+f2+']='''+f2+''',['+f2+']=max(case f2 when '''+f2+'''then f3 end)'
from(select distinct f2 from t1) a
exec('select f1'+@s+'from t1 group by f1')
create table t1(f1 char(5),f2 char(7),f3 varchar(3) )
insert t1 select '00093','2000-01','O'
union all select '00093','2000-07','A+'
union all select '00093','2001-01','A+'
union all select '00092','2000-01','O'
union all select '00092','2000-07','A+'
union all select '00092','2001-01','A+'
go--查询处理
declare @s varchar(8000)
set @s=''
select @s=@s+',['+f2+']='''+f2+''',['+f2+']=max(case f2 when '''+f2+'''then f3 end)'
from(select distinct f2 from t1) a
exec('select f1'+@s+'from t1 group by f1')
go--删除测试表
drop table t1/*--测试结果
f1 2000-01 2000-01 2000-07 2000-07 2001-01 2001-01
----- ------- ------- ------- ------- ------- -------
00092 2000-01 O 2000-07 A+ 2001-01 A+
00093 2000-01 O 2000-07 A+ 2001-01 A+
--*/