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