求表的行列转换 万分感谢

[yuyanliu]

成品检验表QMS
TH001(单别),TH002(单号),TH003(序号),TH014(次数),TH004(检验项目),TH016(结果类型,3数字,4文字),TH017(3数字类型结果),TH018(4文字类型结果)
5801 201008012 0007 0001 2001 3 1.234
5801 201008012 0007 0001 2002 3 2.450
5801 201008012 0007 0001 2003 4 绿色液体
5801 201008012 0007 0001 2010 3 92.300

因每张单据的检验项目TH004不固定 ,求动态行列转换显示成如下格式，万分感谢。

H001(单别),TH002(单号),TH003(序号),TH014(次数), 2001， 2002， 2003， 2010
5801 201008012 0007 0001 1.234 2.450 绿色液体 92.300

[yuyanliu]

[xxfvba]

declare @s varchar(max) select @s=ISNULL(@s+',','')+'MAX(case when Th004='''+TH004+''' then TH018 end) as ['+TH004+']' from QMS group by TH004 set @s='select TH001,TH002,TH003,TH014,'+@s+' from QMS group by TH001,TH002,TH003,TH014' exec(@S)

[xxfvba]

;with T (TH001,TH002,TH003,TH014,TH004,TH016,TH017,TH018) as (select '5801','201008012','0007','0001','2001',3,'1.234','' union all select '5801','201008012','0007','0001','2002', 3, '2.450','' union all select '5801','201008012','0007','0001','2003',4, '','绿色液体' union all select '5801','201008012','0007','0001','2010', 3 ,'92.300','') select TH001,TH002,TH003,TH014,TH004,TH016,TH018=case when isnull(TH017,'')='' then TH018 else TH017 end into #T from T declare @s varchar(max) select @s=ISNULL(@s+',','')+'MAX(case when Th004='''+TH004+''' then TH018 end) as ['+TH004+']' from #T group by TH004 set @s='select TH001,TH002,TH003,TH014,'+@s+' from #T group by TH001,TH002,TH003,TH014' exec(@S) drop table #T

[xxfvba]

;with T (TH001,TH002,TH003,TH014,TH004,TH016,TH017,TH018) as (select '5801','201008012','0007','0001','2001',3,'1.234','' union all select '5801','201008012','0007','0001','2002', 3, '2.450','' union all select '5801','201008012','0007','0001','2003',4, '','绿色液体' union all select '5801','201008012','0007','0001','2010', 3 ,'92.300','') select * into #T from T declare @s varchar(max) select @s=ISNULL(@s+',','')+'MAX(case when Th004='''+TH004+''' then case when isnull(TH017,'''')='''' then TH018 else TH017 end end) as ['+TH004+']' from #T group by TH004 set @s='select TH001,TH002,TH003,TH014,'+@s+' from #T group by TH001,TH002,TH003,TH014' exec(@S)

[中国风]

在此贴已回复过,测测结果是否正确 http://bbs.csdn.net/topics/391910522