sql server 新语法 收藏

1、行转列 PIVOT函数，行转列，列转换UNPIVOT

 select brlx as '姓名',西药费,中成药,中草药 from cc_mzjzzjb_zy a
PIVOT
(
max(a.ysje) for a.zy in(西药费,中成药,中草药)
)b;

select *
from ShoppingCart as C 
PIVOT(count(TotalPrice) FOR [Week] IN([1],[2],[3],[4],[5],[6],[7])) AS T 

https://www.cnblogs.com/linJie1930906722/p/6036714.html
https://www.cnblogs.com/wlsandwho/p/4423956.html
https://www.cnblogs.com/ylbtech/p/8028808.html
https://www.cnblogs.com/sword-successful/p/4814840.html

2.SQL XML

左外连接  LEFT OUTER JOIN 

select dm,ks from  c_dept for xml path

select dm,ks from  c_dept for xml path('Dept')

select dm,ks from  c_dept for xml raw

select dm,ks from  c_dept for xml auto

字符串解析为表

https://www.cnblogs.com/lanyubaicl/p/5671966.html

特殊字符用nvarchar类型，插入的时候字符常量前面加N

create table TbN( itemno NVARCHAR(50));

INSERT INTO TbN(itemno) VALUES (N'A02.201†'  );

比较简单的生成多行数据类似select union

    SELECT * FROM (VALUES(1),(2),(3)) AS V(A)

A
1
2
3

字符串拼接分解

 SELECT stuff( (SELECT ',' + turnOffice  FROM (
  select ( select dm from c_dept  t where t.ks=items) as turnOffice  FROM dbo.[Split_StrByDelimiter](REPLACE( '内一科->内三科->外一科->骨科->妇科','->',','),',')
  ) A FOR XML PATH('')),1,1,'')  as turnOffice

https://blog.csdn.net/sqlserverdiscovery/article/details/79187307

SQL Server 2016新增了string_split函数，专门用来拆分字符串。

SQL Server 2017RTM版，新增函数string_agg

sql 2019没有sql debuger功能了，sql调试功能。T-SQL Debugger 没有这个太坑人了。

https://feedback.azure.com/forums/908035-sql-server/suggestions/35691865-dont-remove-debugging-from-ssms-18-0

https://www.sqlservercentral.com/forums/topic/debug-is-not-visible-in-sql-management-studio

要安装个 SQL Server Data Tools (SSDT)

行号  ROW_NUMBER

SELECT  ROW_NUMBER() over( order by 日期) as rows,
姓名,日期  
from  t