- A+
所属分类:网站建设
行业互转SQL脚本
create table test(id int,name varchar(20),quarter int,profile int) insert into test values(1,'a',1,1000) insert into test values(1,'a',2,2000) insert into test values(1,'a',3,4000) insert into test values(1,'a',4,5000) insert into test values(2,'b',1,3000) insert into test values(2,'b',2,3500) insert into test values(2,'b',3,4200) insert into test values(2,'b',4,5500) select * from test --行转列 select id,name, [1] as "一季度", [2] as "二季度", [3] as "三季度", [4] as "四季度", [5] as "5" from test pivot ( sum(profile) for quarter in ([1],[2],[3],[4],[5]) ) as pvt create table test2(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int) insert into test2 values(1,'a',1000,2000,4000,5000) insert into test2 values(2,'b',3000,3500,4200,5500) select * from test2 --列转行 select id,name,quarter,profile from test2 unpivot ( profile for quarter in ([Q1],[Q2],[Q3],[Q4]) ) as unpvt
sql替换字符串 substring replace
--例子1: update tbPersonalInfo set TrueName = replace(TrueName,substring(TrueName,2,4),'**') where ID = 1 --例子2: update tbPersonalInfo set Mobile = replace(Mobile,substring(Mobile,4,11),'********') where ID = 1 --例子3: update tbPersonalInfo set Email = replace(Email,'chinamobile','******') where ID = 1