面试题 SqlServer知识

现有如下的表数据

 

 

通过sql语句将其改成列转行

 

 

解题思路：

1. 先按num分组排序生成rIndex
2. 再按名称分组score排序生成index
3. 最后将2张分组排序的数据拼起来

select * from 面试题

select name,score1,score2 from(
	select 面试题.Name,面试题.score as score1,r.score as score2,ROW_NUMBER() over(partition by 面试题.Name order by 面试题.score) rIndex2 from 面试题
	inner join(
		select name,ROW_NUMBER() over(partition by num order by num) rIndex,score from 面试题
	) r on  面试题.Name=r.Name
	where r.score<>面试题.score
)a  where rIndex2=1
order by score1 asc

　　

 

了解更多的列转行信息，请查看：SqlServer PIVOT函数快速实现行转列，UNPIVOT实现列转行