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†'  );

转载于:https://www.cnblogs.com/cb168/p/10416685.html