行列转换pivot与unpivot

select ID, uname, dept from user_info
declare @uname varchar(4000)
set @uname=''

--合并列值方法一（循环）
declare @id int
set @id=(select min(id) from User_info)

while @id<=(select max(id) from User_info)
begin
    set @uname=@uname+','+(select uname from User_info where id=@id)
    set @id=@id+1
end
set @uname=stuff(@uname,1,1,'')

print @uname

--合并列值方法二（XML）
select @uname = stuff((select ','+uname from User_Info for xml path('')), 1, 1, '')

exec ('
select '+@uname+' 
--into ##t
from 
(select uname, dept from User_info) as p
pivot
(max(dept) for uname in ('+@uname+')) as pvt')

select * from ##t

exec ('
select uname, dept 
from 
(select * from ##T) p
unpivot
(dept for uname in ('+@uname+'))
as unpvt')