sql server 行转列_精心总结了一套简单实用的SQL脚本（报表制作必备）

于 2020-12-09 07:10:27 发布
阅读量337 收藏 3
点赞数
文章标签： sql server 行转列 sql server存阿拉伯语变成问号 sql 列转行 sql列转行 sql季度函数 行转列sql 变成临时表
596c86c1a0340f280bd3a3a463b171de.png WONDERWARE INTOUCH 上位机组态技术分享平台

d3d25cb3033f209a48fcacfcb0be9897.gif

关注公众号，点击菜单可查看更多内容

工作中有许多比较常用的SQL脚本，今天开始分上下两篇分享给大家。

0f445ead664f68fbfff79e358d1f1506.gif

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

结果：

1f8bffc1fc4b891e76f81d281db12096.png

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

结果：

e27d57b78c9115ceff4ebe47239afc3a.png

0f445ead664f68fbfff79e358d1f1506.gif

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

(提示：可以左右滑动代码)

结果：

22f0af93548bbe3ad5d4dbfe745513ea.png

--列转行
select id,name,quarter,number
from
test2
unpivot
(
number
for quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt

结果：

e82589d981ebc020866b51a691edbce2.png

0f445ead664f68fbfff79e358d1f1506.gif

3、字符串替换SUBSTRING/REPLACE

SELECT REPLACE('abcdefg',SUBSTRING('abcdefg',2,4),'**')

结果：

32227baac0da1fafedc75d61e4c4454d.png

SELECT REPLACE('13512345678',SUBSTRING('13512345678',4,11),'********')
结果：

e3b11d3c28eed6b736d8b2dc94f9fcc3.png

SELECT REPLACE('12345678@qq.com','1234567','******')

结果：

db8f7f10b6cdd315793168c5012dca7d.png

0f445ead664f68fbfff79e358d1f1506.gif

4、查询一个表内相同纪录 HAVING

如果一个ID可以区分的话，可以这么写

SELECT * FROM HR.Employees

结果：

e93989d92a802fd2650d24d3cc2bce5f.png

select * from HR.Employees
where title in (
select title from HR.Employees
group by title
having count(1)>1)

结果：

e94bf0bac82d923536cf1486b6ada908.png

对比一下发现，ID为1,2的被过滤掉了，因为他们只有一条记录

如果需要几列都相同才能区分的话，可以这么写

select * from HR.Employees
where title+titleofcourtesy in
(select title+titleofcourtesy
from HR.Employees
group by title,titleofcourtesy
having count(1)>1)

结果：

698d7d21ac34b0a516b08de989cfb996.png

title在和titleofcourtesy进行拼接后符合条件的就只有ID为6,7,8,9的了

0f445ead664f68fbfff79e358d1f1506.gif

5、把多行SQL数据变成一条多列数据，即新增列

SELECT 
  id,
  name,
  SUM(CASE WHEN quarter=1 THEN number ELSE 0 END) '一季度',
  SUM(CASE WHEN quarter=2 THEN number ELSE 0 END) '二季度',
  SUM(CASE WHEN quarter=3 THEN number ELSE 0 END) '三季度',
  SUM(CASE WHEN quarter=4 THEN number ELSE 0 END) '四季度'
FROM test
GROUP BY id,name

结果：

e27d57b78c9115ceff4ebe47239afc3a.png

我们将原来的4列增加到了6列。细心的朋友可能发现了这个结果和上面的行转列怎么一模一样？其实上面的行转列是省略写法，这种是比较通用的写法。 

0f445ead664f68fbfff79e358d1f1506.gif

6、把多列数据变成一行数据，即减少列 原始数据

2cc66deda22431cede03d825fdc81b40.png

列转行(减少列) 
SELECT NAME,'一季度' Quarter,Max(一季度) Num FROM Test GROUP BY NAME
UNION ALL
SELECT NAME,'二季度' Quarter,Max(二季度) Num FROM Test GROUP BY NAME
UNION ALL
SELECT NAME,'三季度' Quarter,Max(三季度) Num FROM Test GROUP BY NAME
UNION ALL
SELECT NAME,'四季度' Quarter,Max(四季度) Num FROM Test GROUP BY NAME

结果：

c89288f697f631a449b725accd4b3fa8.png

0f445ead664f68fbfff79e358d1f1506.gif

7、表复制

语法1：Insert INTO table(field1,field2,...) values(value1,value2,...) 语法2：Insert into Table2(field1,field2,...) select value1,value2,... from Table1 (要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Table1的字段外，还可以插入常量。) 语法3：SELECT vale1, value2 into Table2 from Table1 (要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。) 语法4：使用导入导出功能进行全表复制。如果是使用【编写查询以指定要传输的数据】，那么在大数据表的复制就会有问题？因为复制到一定程度就不再动了，内存爆了？它也没有写入到表中。而使用上面3种语法直接执行是会马上刷新到数据库表中的，你刷新一下mdf文件就知道了。

0f445ead664f68fbfff79e358d1f1506.gif

8、利用带关联子查询Update语句更新数据

--方法1：
Update Table1
set c = (select c from Table2 where a = Table1.a)
where c is null 

--方法2：
update  A
set  newqiantity=B.qiantity
from  A,B
where  A.bnum=B.bnum

--方法3：
update
(select A.bnum ,A.newqiantity,B.qiantity from A
left join B on A.bnum=B.bnum) AS C
set C.newqiantity = C.qiantity
where C.bnum ='001'

0f445ead664f68fbfff79e358d1f1506.gif

9、连接远程服务器

--方法1：
select *  from openrowset(
'SQLOLEDB',
'server=192.168.0.1;uid=sa;pwd=password',
'SELECT * FROM dbo.test')

--方法2：
select *  from openrowset(
'SQLOLEDB',
'192.168.0.1';
'sa';
'password',
'SELECT * FROM dbo.test')

当然也可以参考以前的示例，建立DBLINK进行远程连接

0f445ead664f68fbfff79e358d1f1506.gif

10、Date 和 Time 样式 CONVERT

CONVERT() 函数是把日期转换为新数据类型的通用函数。

CONVERT() 函数可以用不同的格式显示日期/时间数据。

语法

CONVERT(data_type(length),data_to_be_converted,style)

data_type(length) 规定目标数据类型(带有可选的长度)。data_to_be_converted 含有需要转换的值。style 规定日期/时间的输出格式。

可以使用的 style 值：

Style IDStyle 格式
100 或者 0mon dd yyyy hh:miAM (或者 PM)
101mm/dd/yy
102yy.mm.dd
103dd/mm/yy
104dd.mm.yy
105dd-mm-yy
106dd mon yy
107Mon dd, yy
108hh:mm:ss
109 或者 9mon dd yyyy hh:mi:ss:mmmAM(或者 PM)
110mm-dd-yy
111yy/mm/dd
112yymmdd
113 或者 13dd mon yyyy hh:mm:ss:mmm(24h)
114hh:mi:ss:mmm(24h)
120 或者 20yyyy-mm-dd hh:mi:ss(24h)
121 或者 21yyyy-mm-dd hh:mi:ss.mmm(24h)
126yyyy-mm-ddThh:mm:ss.mmm(没有空格)
130dd mon yyyy hh:mi:ss:mmmAM
131dd/mm/yy hh:mi:ss:mmmAM
SELECT CONVERT(varchar(100), GETDATE(), 0)
--结果：
04  9 2020  7:09PM
SELECT CONVERT(varchar(100), GETDATE(), 1)
--结果：
04/09/20
SELECT CONVERT(varchar(100), GETDATE(), 2)
--结果：
20.04.09
SELECT CONVERT(varchar(100), GETDATE(), 3)
--结果：
09/04/20
SELECT CONVERT(varchar(100), GETDATE(), 4)
--结果：
09.04.20
SELECT CONVERT(varchar(100), GETDATE(), 5)
--结果：
09-04-20
SELECT CONVERT(varchar(100), GETDATE(), 6)
--结果：
09 04 20
SELECT CONVERT(varchar(100), GETDATE(), 7)
--结果：
04 09, 20
SELECT CONVERT(varchar(100), GETDATE(), 8)
--结果：
19:09:52
SELECT CONVERT(varchar(100), GETDATE(), 9)
--结果：
04  9 2020  7:09:52:820PM
SELECT CONVERT(varchar(100), GETDATE(), 10)
--结果：
04-09-20
SELECT CONVERT(varchar(100), GETDATE(), 11)
--结果：
20/04/09
SELECT CONVERT(varchar(100), GETDATE(), 12)
--结果：
200409
SELECT CONVERT(varchar(100), GETDATE(), 13)
--结果：
09 04 2020 19:09:52:820
SELECT CONVERT(varchar(100), GETDATE(), 14)
--结果：
19:09:52:820
SELECT CONVERT(varchar(100), GETDATE(), 20)
--结果：
2020-04-09 19:09:52
SELECT CONVERT(varchar(100), GETDATE(), 21)
--结果：
2020-04-09 19:09:52.820
SELECT CONVERT(varchar(100), GETDATE(), 22)
--结果：
04/09/20  7:09:52 PM
SELECT CONVERT(varchar(100), GETDATE(), 23)
--结果：
2020-04-09
SELECT CONVERT(varchar(100), GETDATE(), 24)
--结果：
19:09:52
SELECT CONVERT(varchar(100), GETDATE(), 25)
--结果：
2020-04-09 19:09:52.820
SELECT CONVERT(varchar(100), GETDATE(), 100)
--结果：
04  9 2020  7:09PM
SELECT CONVERT(varchar(100), GETDATE(), 101)
--结果：
04/09/2020
SELECT CONVERT(varchar(100), GETDATE(), 102)
--结果：
2020.04.09
SELECT CONVERT(varchar(100), GETDATE(), 103)
--结果：
09/04/2020
SELECT CONVERT(varchar(100), GETDATE(), 104)
--结果：
09.04.2020
SELECT CONVERT(varchar(100), GETDATE(), 105)
--结果：
09-04-2020
SELECT CONVERT(varchar(100), GETDATE(), 106)
--结果：
09 04 2020
SELECT CONVERT(varchar(100), GETDATE(), 107)
--结果：
04 09, 2020
SELECT CONVERT(varchar(100), GETDATE(), 108)
--结果：
19:09:52
SELECT CONVERT(varchar(100), GETDATE(), 109)
--结果：
04  9 2020  7:09:52:820PM
SELECT CONVERT(varchar(100), GETDATE(), 110)
--结果：
04-09-2020
SELECT CONVERT(varchar(100), GETDATE(), 111)
--结果：
2020/04/09
SELECT CONVERT(varchar(100), GETDATE(), 112)
--结果：
20200409
SELECT CONVERT(varchar(100), GETDATE(), 113)
--结果：
09 04 2020 19:09:52:820
SELECT CONVERT(varchar(100), GETDATE(), 114)
--结果：
19:09:52:820
SELECT CONVERT(varchar(100), GETDATE(), 120)
--结果：
2020-04-09 19:09:52
SELECT CONVERT(varchar(100), GETDATE(), 121)
--结果：
2020-04-09 19:09:52.820

以上内容，在工作中比较常用，能记住最好。不能记住就收藏起来，在需要的时候查询即可。

6c1a483770ceaee413c37ae5883d312c.png

end

6c1a483770ceaee413c37ae5883d312c.png 53060b1ef97b9458d3054856c4bfe3c8.gif

●InTouch-学习非常实用的手册大全

●InTouch-鼠标悬停自定义下拉菜单(含核心代码·耐心看)毫不逊色于Web网页菜单效果

●InTouch-实时报警、历史报警详细步骤介绍

●Intouch-历史曲线一个不为人知的小“秘密”，真香

●Intouch-自行下拉菜单用法以及系统管理

●Intouch-利用SQL数据库发送邮件真实学习案例

●Intouch-历史报警/操作记录导出为EXCEL实例

●Intouch-历史数据库之报表曲线Historian客户端开发

温馨提示

如果您喜欢本文，请动动小指头分享到朋友圈，想要获得更多技术信息，请关注我。有任何疑问可在留言区 留言。

8870f3021853aa6307cf04010626d6e3.gif af4a0a4065af9f41ce359c1a1f595664.png

我们是一群信息技术爱好者，我们乐于分享技术成果，只要能够帮助到更多的人。我们有共同的理想，那就是诗和远方，期待您加入我们，共创辉煌。

--乔柯里

577a05f512ade7e575a972be93d9aab5.gif 点击 「 阅读原文 」 学习更多~   

我就知道你“在看”

37fda9ae079c16720861256f2a9b7afc.gif
weixin_39669204
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值