目录
文档持续更新中。。。
1、行转列的用法 PIVOT
CREATE table test (id int,name nvarchar(20),quarter int,number int)
insert into test values(1,N'苹果',1,1000)
insert into test values(1,N'苹果',2,2000)
insert into test values(1,N'苹果',3,4000)
insert into test values(1,N'苹果',4,5000)
insert into test values(2,N'梨子',1,3000)
insert into test values(2,N'梨子',2,3500)
insert into test values(2,N'梨子',3,4200)
insert into test values(2,N'梨子',4,5500)
select * from test
结果:
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
结果:
2、列转行的用法 UNPIOVT
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
结果:
3、 SQL Server中使用convert进行日期转换
-
SELECT CONVERT(varchar(100), GETDATE(), 0) -- 05 16 2006 10:57AM
-
SELECT CONVERT(varchar(100), GETDATE(), 1) -- 05/16/06
-
SELECT CONVERT(varchar(100), GETDATE(), 2) -- 06.05.16
-
SELECT CONVERT(varchar(100), GETDATE(), 3) -- 16/05/06
-
SELECT CONVERT(varchar(100), GETDATE(), 4) -- 16.05.06
-
SELECT CONVERT(varchar(100), GETDATE(), 5) -- 16-05-06
-
SELECT CONVERT(varchar(100), GETDATE(), 6) -- 16 05 06
-
SELECT CONVERT(varchar(100), GETDATE(), 7) -- 05 16, 06
-
SELECT CONVERT(varchar(100), GETDATE(), 8) -- 10:57:46
-
SELECT CONVERT(varchar(100), GETDATE(), 9) -- 05 16 2006 10:57:46:827AM
-
SELECT CONVERT(varchar(100), GETDATE(), 10) -- 05-16-06
-
SELECT CONVERT(varchar(100), GETDATE(), 11) -- 06/05/16
-
SELECT CONVERT(varchar(100), GETDATE(), 12) -- 060516
-
SELECT CONVERT(varchar(100), GETDATE(), 13) -- 16 05 2006 10:57:46:937
-
SELECT CONVERT(varchar(100), GETDATE(), 14) -- 10:57:46:967
-
SELECT CONVERT(varchar(100), GETDATE(), 20) -- 2006-05-16 10:57:47
-
SELECT CONVERT(varchar(100), GETDATE(), 21) -- 2006-05-16 10:57:47.157
-
SELECT CONVERT(varchar(100), GETDATE(), 22) -- 05/16/06 10:57:47 AM
-
SELECT CONVERT(varchar(100), GETDATE(), 23) -- 2006-05-16
-
SELECT CONVERT(varchar(100), GETDATE(), 24) -- 10:57:47
-
SELECT CONVERT(varchar(100), GETDATE(), 25) -- 2006-05-16 10:57:47.250
-
SELECT CONVERT(varchar(100), GETDATE(), 100) -- 05 16 2006 10:57AM
-
SELECT CONVERT(varchar(100), GETDATE(), 101) -- 05/16/2006
-
SELECT CONVERT(varchar(100), GETDATE(), 102) -- 2006.05.16
-
SELECT CONVERT(varchar(100), GETDATE(), 103) -- 16/05/2006
-
SELECT CONVERT(varchar(100), GETDATE(), 104) -- 16.05.2006
-
SELECT CONVERT(varchar(100), GETDATE(), 105) -- 16-05-2006
-
SELECT CONVERT(varchar(100), GETDATE(), 106) -- 16 05 2006
-
SELECT CONVERT(varchar(100), GETDATE(), 107) -- 05 16, 2006
-
SELECT CONVERT(varchar(100), GETDATE(), 108) -- 10:57:49
-
SELECT CONVERT(varchar(100), GETDATE(), 109) -- 05 16 2006 10:57:49:437AM
-
SELECT CONVERT(varchar(100), GETDATE(), 110) -- 05-16-2006
-
SELECT CONVERT(varchar(100), GETDATE(), 111) -- 2006/05/16
-
SELECT CONVERT(varchar(100), GETDATE(), 112) -- 20060516
-
SELECT CONVERT(varchar(100), GETDATE(), 113) -- 16 05 2006 10:57:49:513
-
SELECT CONVERT(varchar(100), GETDATE(), 114) -- 10:57:49:547
-
SELECT CONVERT(varchar(100), GETDATE(), 120) -- 2006-05-16 10:57:49 --常用
-
SELECT CONVERT(varchar(100), GETDATE(), 121) -- 2006
-
SELECT CONVERT(varchar(100), GETDATE(), 126) -- 2006-05-16T10:57:49.827
-
SELECT CONVERT(varchar(100), GETDATE(), 130) -- 18 ???? ?????? 1427 10:57:49:907AM
-
SELECT CONVERT(varchar(100), GETDATE(), 131) -- 18/04/1427 10:57:49:920AM