Sql Server 列轉行 Pivot使用，sqlpivot

Sql Server 列轉行 Pivot使用，sqlpivot

今天正好做 資料展示，用到了列轉行，列轉行有多種方式，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') aset @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 @sqlEXEC(@sql) 

執行之後，就如上面的的預期的顯示結果一樣了，

注意：

Pivot文法結構：

Pivot  （A） for B in(C)

A：Max(Total)，表示要顯示的合計值，

B：Monthly，未經處理資料的列頭，就是要把它的資料轉化為列的欄位的名字

C：動態列 B的資料內容

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

方案二：

也可以用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或者預存程序，稍稍複雜一些。