多行合并为一行

CREATE TABLE [dbo].[表1] (
[sCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[表2] (
[sCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[sQuality] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[fNum] [int] NULL,[Gr]  [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
-----------------------------------sql语句

declare @sqlstr varchar (2000),@sQuality varchar (50),@iLoop int
select @sqlstr=''select @iLoop=1
Declare ItemList Cursor for select sQuality from table2 group by sQuality
Open ItemList
Fetch next from ItemList into @sQuality
while @@Fetch_STATUS=0
BEGIN  
select @sqlstr=isnull(@sqlstr,'')+ ',(select top 1 sQuality from table2 where a.scode=scode and sQuality='''
+isnull(@sQuality,'')+''' group by sQuality) Item'+cast(@iLoop as varchar) +',
(select fnum from table2 where a.scode=scode and sQuality='''+isnull(@sQuality,'')+''' group by fnum) ItemNum'+cast(@iLoop as varchar)+',
(select Gr from table2 where a.scode=scode and sQuality='''+isnull(@sQuality,'')+''' group by Gr) ItemStr'+cast(@iLoop as varchar)
select @iLoop=@iLoop+1 
Fetch next from ItemList into @sQuality
END
close ItemList
DEALLOCATE ItemList
select @sqlstr= 'select scode' +isnull(@sqlstr,'') + ' from table2 a where scode in (select scode from table1) group by scode'
Exec (@sqlstr)