SQL SERVER常用SQL：行号，分组行号，行转列

ROW_NUMBER

抓取行号，可用于分页查询

SELECT new_name, new_type, CreatedOn, 
ROW_NUMBER() OVER(ORDER BY CreatedOn) as new_number 
FROM new_test1Base 

ROW_NUMBER() OVER(PARTITION BY […])

分组之后排序

SELECT new_name, new_type, CreatedOn, 
ROW_NUMBER() OVER(PARTITION BY new_name ORDER BY CreatedOn) as new_number 
FROM new_test1Base 

PIVOT

列转行
语法：

SELECT <non-pivoted column>,  
       <list of pivoted column>  
FROM  
(<SELECT query  to produces the data>)  
    AS <alias name>  
PIVOT  
(  
<aggregation function>(<column name>)  
FOR  
[<column name that  become column headers>]  
    IN ( [list of  pivoted columns])  
  
) AS <alias name  for  pivot table> 

原始查询如下图所示

行转列，同时实现了分组汇总

如果行（转换后的列）数据不重复，那么随意选择一个聚合函数，转换之后的列数=转之前行数
如果行数据重复，那么选择一个聚合函数之后，比如MAX，转换之后的列<转之前行数。
如下图，只会保留值最大的行，再转为列