工作中有许多比较常用的理保SQL脚本,今天开始分几章分享给大家。常存
( , (), , )
(,N,,)
(,N,,)
(,N,,)
(,N,,)
(,N,,)
(,N,,)
(,N,,)
(,N,,)
*
结果:
select ID,NAME,
[1] as '一季度',
[2] as '二季度',
[3] as '三季度',
[4] as '四季度'
from
test
pivot
(
sum(number)
for quarter in
([1],[2],[3],[4])
)
as pvt
结果:
create table test2
(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)
insert into test2 values(1,'苹果',1000,2000,4000,5000)
insert into test2 values(2,'梨子',3000,3500,4200,5500)
select * from test2
(提示:可以左右滑动代码)
结果:
--列转行
select id,name,quarter,number
from
test2
unpivot
(
number
for quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt
结果:
SELECT REPLACE('abcdefg',SUBSTRING('abcdefg',2,4),'**')
结果:
SELECT REPLACE('13512345678',SUBSTRING('13512345678',4,11),'(责任编辑:综合)
奏响精准扶贫旋律 中国太保2018“乐行天下”新年音乐会在沪举行