create table #a (a int , b char(4))insert into #a select 1,'张三'insert into #a select 2,'李四'insert into #a select 3,'王五'select * from #aa           b----------- ----1           张三2           李四3           王五(3 行受影响)  --行转列，步骤：''+张三+],[+王五+],[+李四declare @sql varchar(100)select @sql=isnull(@sql+'],[','')+b from #a select  @sql='['+@sql+']'select  @sql as 结果1go结果1----------------------------------------------------------------------------------------------------[张三],[李四],[王五](1 行受影响)--结果为NULL，所以用ISNULLdeclare @sql varchar(100)select @sql=@sql+b from #aselect @sqlNULL----------------------------------------------------------------------------------------------------NULL(1 行受影响)--结果，同上declare @sql varchar(100)set @sql=''select @sql=@sql+'],['+b from #aselect @sql=right(@sql,len(@sql)-2)+']'select @sql as 结果2go 结果2----------------------------------------------------------------------------------------------------[张三],[李四],[王五](1 行受影响) --结果，同上declare @sql varchar(100)set @sql=''select @sql=stuff((select '],['+b  from #a for xml path('')),1,2,'') +']'select  @sql as 结果3go 结果3----------------------------------------------------------------------------------------------------[张三],[李四],[王五](1 行受影响) --结果，同上。declare @sql varchar(8000)select @sql=coalesce(@sql+'],[','')+b from #aselect @sql='['+@sql+']'select  @sql as 结果4go  结果4----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------[张三],[李四],[王五](1 行受影响) --思路一样都是列行互转，结果不一样。declare @b varchar(100)declare @c varchar(100)set @c='select *  from (select b from #a ) as e  pivot (max(b)  for b in ([张三],[李四],[王五]))  as c'exec(@c)  张三   李四   王五---- ---- ----张三   李四   王五(1 行受影响)

 

create table #a(id char(2) ,a char(2),b char(2))insert into #a values('1','2','3')insert into #a values('1','3','3')insert into #a values('1','4','3')insert into #a values('2','1','5')insert into #a values('2','2','5')select a.id,[a]=stuff((select ',' +b from #a as a1 where a1.id=a.id  FOR XML PATH('')),1,1,' ' ),b from #a agroup by a.id,b;drop table #a;

结果：

id a     b----------------1  3,3,3 3 2  5,5   5(2 行受影响))