if object_id('tb') is not nulldrop table tbgocreate table tb(id int, [value] varchar(10))insert into tb values(1, 'aa')insert into tb values(1, 'bb')insert into tb values(2, 'aaa')insert into tb values(2, 'bbb')insert into tb values(2, 'ccc')goDECLARE @sql varchar(8000)SET @sql = 'select m.id 'select @sql = @sql + ' , max(case px when ' + cast(px as varchar) + ' then n.[value] else '''' end) [value' + cast(px as varchar) + ']' from (select distinct p.px from (select *, px = row_number() over (partition by [id] order by [id]) from tb) p) as aSET @sql = @sql + ' from tb m, (select *, px = row_number() over (partition by [id] order by [id]) from tb) n where m.id = n.id group by m.id order by m.id'--print @sqlexec (@sql)/*id value1 value2 value3----------- ---------- ---------- ----------1 aa bb 2 aaa bbb ccc(2 行受影响)*/--带','号select id, [value] = stuff((select ' ' + [value] from tb t where id = tb.id for xml path('')),1,1,'') from tb group by id/*id value----------- ----------------1 aa bb2 aaa bbb ccc(2 行受影响)*/drop table tb
