SQL Server 行列转换示例-优快云博客

本文链接：https://blog.youkuaiyun.com/emailqjc/article/details/5293894

本文通过实例演示了SQLServer中Pivot和UnPivot函数的使用方法，包括从宽表到窄表、从窄表到宽表等多种转换场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL Server 行列转换示例收藏
SQL Server的行列转换功能非常实用，但是由于其语法不好懂，使很多初学者都不愿意使用它。下面我就用示例的形式，逐一展现Pivot和UnPivot的魅力。如下图

由于近期优快云的图片服务器不稳定，如果图片挂了，请读者访问图片原地址：

http://img242.ph.126.net/4isS79TpI8jxQViyLD410Q==/2136958023189057751.jpg

1.从Wide Table of Months 转换到 Narrow Table的示例

view plaincopy to clipboardprint?select [Year],[Month],[Sales] from ( select * from MonthsTable )p unpivot ( [Sales] for [Year] in ([2001],[2002],[2003]) )t order by [Year] 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的示例

view plaincopy to clipboardprint?select * from ( select * from NarrowTable )p pivot ( Sum(Sales) for [Month] in ([Jan],[Feb],[Mar]) )t 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的示例

view plaincopy to clipboardprint?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 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的示例

view plaincopy to clipboardprint?
select [Year],[Month],[Sales] from
(
    select * from YearTable
)p
unpivot
(
    Sales for [Month] in ([Jan],[Feb],[Mar])
)t
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的示例

view plaincopy to clipboardprint?select * from ( select * from NarrowTable )p pivot ( Sum(Sales) for [Year] in ([2001],[2002],[2003]) )t 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的示例

view plaincopy to clipboardprint?
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