实际工作用到的：关于PIOVT和UNPIOVT的用法

Normally, we select as below

SELECT @@SPID AS SPID,
CASE quoted_identifier
WHEN 1 THEN 'ON' ELSE 'OFF' END QUOTED_IDENTIFIER,
CASE arithabort
WHEN 1 THEN 'ON' ELSE 'OFF' END ARITHABORT,
CASE ansi_null_dflt_on
WHEN 1 THEN 'ON' ELSE 'OFF' END ANSI_NULL_DFLT_ON,
CASE ansi_defaults
WHEN 1 THEN 'ON' ELSE 'OFF' END ANSI_DEFAULTS ,
CASE ansi_warnings
WHEN 1 THEN 'ON' ELSE 'OFF' END ANSI_WARNINGS,
CASE ansi_padding
WHEN 1 THEN 'ON' ELSE 'OFF' END ANSI_PADDING,
CASE ansi_nulls
WHEN 1 THEN 'ON' ELSE 'OFF' END ANSI_NULLS,
CASE concat_null_yields_null
WHEN 1 THEN 'ON' ELSE 'OFF' END CONCAT_NULL_YIELDS_NULL
FROM sys.aaa
WHERE session_id = @@SPID 

Then we will get:

Let us schedule a step to convert Column Name to Row Value

SELECT SPID,VALUE,ANSI_SETTING
FROM (
    SELECT @@SPID AS SPID,
        CASE quoted_identifier
            WHEN 1 THEN 'ON' ELSE 'OFF' END QUOTED_IDENTIFIER,
        CASE arithabort
            WHEN 1 THEN 'ON' ELSE 'OFF' END ARITHABORT,
        CASE ansi_null_dflt_on
            WHEN 1 THEN 'ON' ELSE 'OFF' END ANSI_NULL_DFLT_ON,
        CASE ansi_defaults
            WHEN 1 THEN 'ON' ELSE 'OFF' END ANSI_DEFAULTS ,
        CASE ansi_warnings
            WHEN 1 THEN 'ON' ELSE 'OFF' END ANSI_WARNINGS,
        CASE ansi_padding
            WHEN 1 THEN 'ON' ELSE 'OFF' END ANSI_PADDING,
        CASE ansi_nulls
            WHEN 1 THEN 'ON' ELSE 'OFF' END ANSI_NULLS,
        CASE concat_null_yields_null
            WHEN 1 THEN 'ON' ELSE 'OFF' 
        END CONCAT_NULL_YIELDS_NULL
    FROM sys.aaaa
    WHERE session_id = @@SPID 
    ) P
UNPIVOT 
(
    VALUE FOR ANSI_SETTING IN
    (
        QUOTED_IDENTIFIER,ARITHABORT,ANSI_NULL_DFLT_ON,
        ANSI_DEFAULTS,ANSI_WARNINGS,
        ANSI_PADDING,ANSI_NULLS,CONCAT_NULL_YIELDS_NULL
    )
) AS unpvt

The result as below:

Let us make a comparison

We need know that ‘UnPIOVT’ is not totally Inverse operations of ‘PIOVT’. It will not show the “NULL” value.

新增内容：
有如下数据
R160005320 191.366667 201611Amount
R160005320 191.366667 201611Amount
R160005320 191.366667 201611Amount
R160005320 191.366667 201611Amount
R160005320 191.366667 201611Amount
R160005322 167.741935 201608Amount
R160005322 167.741935 201608Amount
R160005322 167.741935 201608Amount
R160005322 167.741935 201608Amount
R160005322 167.741935 201608Amount
R160005322 167.741935 201608Amount
R160005322 167.741935 201608Amount

可以看出：每一个ContractId 对应着不同月份的水电读数，现在可以将行转换为列并赋值

看原脚本：

select ContractID,ISNULL(DayRentPrice,0) DayRentPrice ,convert(nvarchar(6),bizdate,112)+'Amount' yearmonth   
from ContractCalendar
where BizDate>='2016-6-1' and BizDate<'2016-12-1' and  StoreID='BJOP070'  and InputBillTag=1

得出的结果是：

修改语句：

declare  @storeId varchar(50) = '';
select  @storeId =StoreID  from store where storename  like '%亮马水晶%'; 

with cte as (

select ContractID,ISNULL(DayRentPrice,0) DayRentPrice ,convert(nvarchar(6),bizdate,112)+'Amount' yearmonth   
from ContractCalendar
where BizDate>='2016-6-1' and BizDate<'2016-12-1' and  StoreID='BJOP070'  and InputBillTag=1
) 
  select ContractID,ISNULL(t.[201606Amount],0)  as [201606Amount],

     ISNULL(t.[201607Amount],0)  as [201607Amount],
     ISNULL(t.[201608Amount],0)  as [201608Amount],
     ISNULL(t.[201609Amount],0)  as [201609Amount],
     ISNULL(t.[201610Amount],0)  as [201610Amount],
     ISNULL(t.[201611Amount],0)  as [201611Amount]  

  from cte pivot(sum(dayrentprice)
  for yearmonth in([201606Amount],[201607Amount],[201608Amount],[201609Amount],[201610Amount],[201611Amount])) t

得到如下结果：

此处用到的是piovt