![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
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')
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')