有如下两表：
使用 pivot 进行“行列转换”，效果：
脚本：
declare @sql varchar(1000) declare @col varchar(1000) select @col = ISNULL(@col + ‘,‘,‘‘) + QUOTENAME(r.Name) from dbo.[User] u inner join dbo.[Role] r on u.RoleID = r.ID group by r.Name --先确定要转换的列名 print @col set @sql = ‘ select * from ( select u.ID, r.Name, u.UserName from dbo.[User] u join dbo.[Role] r on u.RoleID = r.ID ) rg pivot (max(UserName) for rg.Name in (‘ + @col + ‘)) as pvt‘ print(@sql) exec(@sql)
隐藏 ID 列，将 select * from 修改为 select ‘ + @col + ‘ from