SQL Server 行列转换示例

SQL Server的行列转换功能非常实用，但是由于其语法不好懂，使很多初学者都不愿意使用它。下面我就用示例的形式，逐一展现Pivot和UnPivot的魅力。如下图
 
1.从Wide Table of Months 转换到 Narrow Table的示例
select [Year],[Month],[Sales] from
(
select * from MonthsTable
)p
unpivot
(
[Sales] for [Year] in ([2001],[2002],[2003])
)t
order by [Year]
2.从Narrow Table 转换到 Wide Table of Years的示例
select * from
(
select * from NarrowTable
)p
pivot
(
Sum(Sales) for [Month] in ([Jan],[Feb],[Mar])
)t  
3.从Wide Table of Months 转换到 Wide Table of Years的示例
with d as
(
select [Year],[Month],[Sales] from
(
select * from MonthsTable
)p
unpivot
(
[Sales] for [Year] in ([2001],[2002],[2003])
)t
)
select * from
(
select * from d
)p
pivot
(
Sum(Sales) for [Month] in ([Jan],[Feb],[Mar])
)t  
4.从Wide Table of Years 转换到 Narrow Table的示例
select [Year],[Month],[Sales] from
(
select * from YearTable
)p
unpivot
(
Sales for [Month] in ([Jan],[Feb],[Mar])
)t  
5.从Narrow Table 转换到 Wide Table of Month的示例
select * from
(
select * from NarrowTable
)p
pivot
(
Sum(Sales) for [Year] in ([2001],[2002],[2003])
)t  
6.从Wide Table of Years 转换到 Wide Table of Month的示例
with d as
(
select [Year],[Month],[Sales] from
(
select * from YearTable
)p
unpivot
(
Sales for [Month] in ([Jan],[Feb],[Mar])
)t
)
select * from
(
select * from d
)p
pivot
(
Sum(Sales) for [Year] in ([2001],[2002],[2003])
)t
如需转载，请注明本文原创自CSDN TJVictor专栏：http://blog.csdn.net/tjvictor