sql server列转行的几种方法

方法一，临时变量：

declare @temp nvarchar(max)=''

select @temp=coalesce(@temp,'')+Location+','
from(
  select distinct Location from dbo.WG_SP_IllegalBroadcast
) a

set @temp=substring(@temp,1,len(@temp)-1)

print @temp

 方法二：利用xml path操作：

select  stuff((select distinct ','+Location from dbo.WG_SP_IllegalBroadcast
for xml path('')),1,1,'') 

 方法三：利用游标操作：

  使用游标的顺序： 声名游标、打开游标、读取数据、关闭游标、删除游标。

declare @location nvarchar(50)
declare cursor_fruit CURSOR for
   select distinct Location from dbo.WG_SP_IllegalBroadcast
open cursor_fruit
fetch next from cursor_fruit
into @location
while @@FETCH_STATUS=0
begin
print @location
FETCH NEXT FROM cursor_fruit
into @location
end

close cursor_fruit
deallocate cursor_fruit