MSSQL 的 PIVOT 和 UNPIVOT 函数

1.   使用POVIT首先你需要在FROM子句内定义2个表：

      A.一个称为源表(SourceTable)。

      B.另一个称为数据透视表(PivotTable)。

语法：
SELECT  
<未透视的列>,  
 
[第一个透视列] AS <列别名>,   
[第二个透视列] AS <列别名>,  
...  
[最后一个透视列] AS <列别名> 
 
FROM (  <SELECT查询> ) AS <源表> 
 
PIVOT 

(   
        <聚合函数>(<列>)  
        FOR [<需要转换为行的列>] IN

        ( [第一个透视列], [第二个透视列],  ...   [最后一个透视列]  )  

) AS <数据透视表> 

<可选的 ORDER BY 子句>;

eg:

CREATE TABLE [dbo].[tbl01](
[id] [int] NULL,
[dt] [datetime] NULL,
[num] [numeric](18, 4) NULL,
[str] [nvarchar](250) NULL
) ON [PRIMARY]


;with cte as
(
  select year(dt) as yr, month(dt) as mh, num from tbl01 
  -- 聚合字段(num), 列名字段(mh), 其他字段可视为分组字段(如:yr)
)
select 
    yr,
    [1] as [Jan], [2] as [feb], [3] as [mar], [4] as [apr], 
[5] as [may], [6] as [jun], [7] as [jul], [8] as [aug], 
[9] as [sep], [10] as [oct], [11] as [nov], [12] as [dec]
from cte
pivot 
(  
    avg(num) for mh in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) as T

order by yr

注释：

   1.  avg(num)  行转列后，新列的纪录值， 需要用函数聚合。  -----（结果类似是纪录的值）

    2. mh  行转列时，列名来源，也就是说，该字段包含了所有新转列的列名称。   -----（结果类似是纪录的字段）

UNPIVOT的子句没有聚合函数

SELECT   
<未逆透视的列>,  

[合并后的列] AS <列别名>,  
[行值的列名] AS <列别名> 
 
FROM (<SELECT查询>) AS <源表> 
 
UNPIVOT 
(  
     <行值的列名>  FOR <将原来多个列合并到单个列的列名> IN 
     ([第一个合并列], [第二个合并列],  ...  [最后一个合并列])  
) AS <数据逆透视表> 
<可选的ORDER BY子句>; 

透视列名加上 [] .

CREATE TABLE [dbo].[tbl02](
[id] [int] NULL,
[str] [nvarchar](50) NULL,
[dt] [datetime] NULL,
[Jan] [decimal](18, 4) NULL,
[Feb] [decimal](18, 4) NULL,
[Mar] [decimal](18, 4) NULL,
[Apr] [decimal](18, 4) NULL,
[May] [decimal](18, 4) NULL,
[Jun] [decimal](18, 4) NULL,
[Jul] [decimal](18, 4) NULL,
[Aug] [decimal](18, 4) NULL,
[Sep] [decimal](18, 4) NULL,
[Oct] [decimal](18, 4) NULL,
[Nov] [decimal](18, 4) NULL,
[Dec] [decimal](18, 4) NULL
) ON [PRIMARY]


select id, dt, str, 列名, 数值 from  tbl02
unpivot 
(
   数值 for 列名 in (
       [Jan], [feb], [mar], [apr], 
       [may], [jun], [jul], [aug], 
       [sep], [oct], [nov], [dec])
) as T

注释：

   1.  数值  列转行后，新列名为 [数值] 的纪录值， 这里不需要聚合函数。  

    2. 列名  列转行后，该列也是一个纪录值，原来的列名称变成了新 [列名] 的纪录。