SQL Server 行列转换示例

最新推荐文章于 2023-08-21 22:00:16 发布

原创最新推荐文章于 2023-08-21 22:00:16 发布 · 4.8k 阅读

12 ·

CC 4.0 BY-SA版权

文章标签：

#sql server #table #pivot #服务器

Sql Server 2008 专栏收录该内容

54 篇文章

订阅专栏

本文通过六个示例详细介绍了如何使用SQLServer的Pivot和UnPivot功能进行数据的行列转换，包括从宽表到窄表及从窄表到宽表的转换方法。

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

聚合图

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

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

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