Sql Server 列转行 Pivot使用

 

今天正好做 数据展示,用到了列转行,行转列有多种方式,Pivot是其中的一种,Povit 是sql server 2005以后才出现的功能,

下面的业务场景:

每个月,进货渠道的总计数量【Total】,有中文,英文年月,等数据列,

原始数据如下:

技术分享

需求:

技术分享

需要把数据按每一年的1月到12月展示成一行,如上图,怎么办?Povit排上用场了

有的年,可能不是每个月都有,也就是动态列的生成了。

首先要做的就是构建1到12月,

DECLARE @temp NVARCHAR(max)=‘‘
SELECT @temp=COALESCE(@temp,‘‘)+ [+  Monthly+],
FROM 
(SELECT DISTINCT Monthly FROM  [DMS_SourceofBusiness]
WHERE Yearly=2006
) a
set @temp=SUBSTRING(@temp,1,LEN(@temp)-1)

查询一下是否生成了呢?
SELECT @temp

果然如此,

技术分享

下一步就是把这些动态列作为生成数据的列,

 

DECLARE @sql NVARCHAR(max)=‘‘
SET @sql=
SELECT Source,Yearly+@temp+ from
(SELECT Source,Yearly,Monthly,Total FROM  [dbo].[DMS_SourceofBusiness])c
 pivot( MAX(Total)for Monthly IN(+ @temp+
))b 
where  Yearly=‘‘2006‘‘ and Source like‘‘%Customer walk-in%‘‘

PRINT @sql
EXEC(@sql) 

 

执行之后,就如上面的的预期的显示结果一样了,

注意:

Pivot语法结构:

Pivot  (A) for B in(C)

A:Max(Total),表示要显示的合计值,

B:Monthly,原始数据的列头,就是要把它的数据转化为列的字段的名字

C:动态列

-------------------------

方案二:

也可以用case when 来解决,但是这样的动态的列就会变成固定的列,

应用场景:月份:1-12个月,星期(周一到周天),季度(Q1到Q4)等等

代码如下:

 SELECT RTRIM(LTRIM(SOB.Source))Source,SOB.Yearly,
 MAX(CASE WHEN SUBSTRING(SOB.Monthly,6,2)=01 THEN SOB.Total ELSE 0 END  ) AS M01
 ,MAX(CASE WHEN SUBSTRING(SOB.Monthly,6,2)=02 THEN SOB.Total ELSE 0 END  ) M02
 ,MAX(CASE WHEN SUBSTRING(SOB.Monthly,6,2)=03 THEN SOB.Total ELSE 0 END  ) M03
 ,MAX(CASE WHEN SUBSTRING(SOB.Monthly,6,2)=04 THEN SOB.Total ELSE 0 END  ) M04
 ,MAX(CASE WHEN SUBSTRING(SOB.Monthly,6,2)=05 THEN SOB.Total ELSE 0 END  ) M05
 ,MAX(CASE WHEN SUBSTRING(SOB.Monthly,6,2)=06 THEN SOB.Total ELSE 0 END  ) M06
 ,MAX(CASE WHEN SUBSTRING(SOB.Monthly,6,2)=07 THEN SOB.Total ELSE 0 END  ) M07
 ,MAX(CASE WHEN SUBSTRING(SOB.Monthly,6,2)=08 THEN SOB.Total ELSE 0 END  ) M08
 ,MAX(CASE WHEN SUBSTRING(SOB.Monthly,6,2)=09 THEN SOB.Total ELSE 0 END  ) M09
 ,MAX(CASE WHEN SUBSTRING(SOB.Monthly,6,2)=10 THEN SOB.Total ELSE 0 END  ) M10
 ,MAX(CASE WHEN SUBSTRING(SOB.Monthly,6,2)=11 THEN SOB.Total ELSE 0 END  ) M11
 ,MAX(CASE WHEN SUBSTRING(SOB.Monthly,6,2)=12 THEN SOB.Total ELSE 0 END  ) M12
  FROM [dbo].[DMS_SourceofBusiness] SOB WITH(NOLOCK)
WHERE SOB.Yearly =2015
GROUP BY Source,SOB.Yearly


显示数据格式:

技术分享

 

总结:

根据不同的类型选择不同的解决方法,

使用Pivot是比较通用的一种方式,但是写sql或者存储过程,稍稍复杂一些。

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。