当前位置： 移动技术网 > IT编程>数据库>MSSQL > SQL行转列和列转行代码详解

SQL行转列和列转行代码详解

2017年11月02日  | 移动技术网IT编程  | 我要评论

海贼王娜米3,cd激光头,钟瑞

行列互转，是一个经常遇到的需求。实现的方法，有case when方式和2005之后的内置pivot和unpivot方法来实现。
在读了技术内幕那一节后，虽说这些解决方案早就用过了，却没有系统性的认识和总结过。为了加深认识，再总结一次。
行列互转，可以分为静态互转，即事先就知道要处理多少行（列）;动态互转,事先不知道处理多少行(列)。

--创建测试环境
use tempdb;
go
if object_id('dbo.orders') is not null
 drop table dbo.orders;
go
create table dbo.orders
(
 orderid  int    not null primary key nonclustered,
 orderdate datetime  not null,
 empid   int    not null,
 custid  varchar(5) not null,
 qty    int    not null
);
create unique clustered index idx_orderdate_orderid
 on dbo.orders(orderdate, orderid);
insert into dbo.orders(orderid, orderdate, empid, custid, qty)
 values(30001, '20020802', 3, 'a', 10);
insert into dbo.orders(orderid, orderdate, empid, custid, qty)
 values(10001, '20021224', 1, 'a', 12);
insert into dbo.orders(orderid, orderdate, empid, custid, qty)
 values(10005, '20021224', 1, 'b', 20);
insert into dbo.orders(orderid, orderdate, empid, custid, qty)
 values(40001, '20030109', 4, 'a', 40);
insert into dbo.orders(orderid, orderdate, empid, custid, qty)
 values(10006, '20030118', 1, 'c', 14);
insert into dbo.orders(orderid, orderdate, empid, custid, qty)
 values(20001, '20030212', 2, 'b', 12);
insert into dbo.orders(orderid, orderdate, empid, custid, qty)
 values(40005, '20040212', 4, 'a', 10);
insert into dbo.orders(orderid, orderdate, empid, custid, qty)
 values(20002, '20040216', 2, 'c', 20);
insert into dbo.orders(orderid, orderdate, empid, custid, qty)
 values(30003, '20040418', 3, 'b', 15);
insert into dbo.orders(orderid, orderdate, empid, custid, qty)
 values(30004, '20020418', 3, 'c', 22);
insert into dbo.orders(orderid, orderdate, empid, custid, qty)
 values(30007, '20020907', 3, 'd', 30);
go

行转列-静态方案：

--行转列的静态方案一：case when,兼容sql2000
select custid,
sum(case when year(orderdate)=2002 then qty end) as [2002],
sum(case when year(orderdate)=2003 then qty end) as [2003],
sum(case when year(orderdate)=2004 then qty end) as [2004]
from orders
group by custid;
go
--行转列的静态方案二：pivot,sql2005及以后版本
select *
from (select custid,year(orderdate) as years,qty from orders) as ord
pivot(sum(qty) for years in([2002],[2003],[2004]))as p
go

行转列-动态方案：加入了xml处理和sql注入预防判断

--既然是用到了动态sql,就有一个老话题:sql注入。建一个注入性字符的判断函数。
create function [dbo].[fn_checksqlinjection]
(
 @col nvarchar(4000)
)
returns bit --如果存在可能的注入字符返回true,反之返回false
as
begin
declare @result bit;
 if 
   upper(@col) like upper(n'%0x%')
 or upper(@col) like upper(n'%;%')
 or upper(@col) like upper(n'%''%')
 or upper(@col) like upper(n'%--%')
 or upper(@col) like upper(n'%/*%*/%')
 or upper(@col) like upper(n'%exec%')
 or upper(@col) like upper(n'%xp_%')
 or upper(@col) like upper(n'%sp_%')
 or upper(@col) like upper(n'%select%')
 or upper(@col) like upper(n'%insert%')
 or upper(@col) like upper(n'%update%')
 or upper(@col) like upper(n'%delete%')
 or upper(@col) like upper(n'%truncate%')
 or upper(@col) like upper(n'%create%')
 or upper(@col) like upper(n'%alter%')
 or upper(@col) like upper(n'%drop%')
 set @result=1
 else
 set @result=0
 return @result
end
go
--行转列的动态方案一：case when,兼容sql2000
declare @t table (years int not null primary key);
insert into @t 
select distinct year(orderdate) from orders;
declare @y int;
set @y=(select min(years) from @t);
declare @sql nvarchar(4000)=n'';
while @y is not null
begin
 set @sql=@sql+n',sum(case when year(orderdate)='+cast(@y as nvarchar(4)) +n' then qty end) as '+quotename(@y);
 set @y=(select min(years) from @t where years>@y);
end
if dbo.fn_checksqlinjection(@sql)=0
set @sql=n'select custid'+@sql+n' from orders group by custid'
print @sql
exec sp_executesql @sql
go
--行转列的动态方案二：pivot,sql2005及以后版本
declare @t table (years int not null primary key);
insert into @t 
select distinct year(orderdate) from orders;
declare @y int;
set @y=(select min(years) from @t);
declare @sql nvarchar(4000)=n'';
  --这里使用了xml处理来处理类组字符串
set @sql=stuff((select n','+quotename(years) from @t
 for xml path('')),1,1,n'');
if dbo.fn_checksqlinjection(@sql)=0
set @sql=n'select * from (select distinct custid,year(orderdate) as years,qty from orders) as ord
pivot(sum(qty) for years in('+@sql+n'))as p';
print @sql;
exec sp_executesql @sql;
go

列转行：

--列转行的静态方案：unpivot,sql2005及以后版本
select * from dbo.pvtcustorders
select custid,years,qty
from dbo.pvtcustorders
unpivot(qty for years in([2002],[2003],[2004]))as up
go
--列转行的动态方案：unpivot,sql2005及以后版本
--因为行是动态所以这里就从information_schema.columns视图中获取列来构造行，同样也使用了xml处理。
declare @sql nvarchar(4000)=n'';
set @sql=stuff((select n','+quotename(column_name ) from information_schema.columns
where ordinal_position>1 and table_name='pvtcustorders'
for xml path('')),1,1,n'')
set @sql=n'select custid,years,qty
     from dbo.pvtcustorders
     unpivot(qty for years in('+@sql+'))as up';
print @sql;
exec sp_executesql @sql;

总结

以上就是本文关于sql行转列和列转行代码详解的全部内容，希望对大家有所帮助。感兴趣的朋友可以继续参阅本站：mysql中find_in_set()和in区别简析、、mysql子查询和嵌套查询优化实例解析等，有什么问题可以随时留言，小编会及时回复大家的。感谢朋友们对移动技术网网站的支持！

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网