SQL如何实现行转列和列转行

共计 4569 个字符，预计需要花费 12 分钟才能阅读完成。

这篇文章给大家分享的是有关 SQL 如何实现行转列和列转行的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，一起跟随丸趣 TV 小编过来看看吧。

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

-- 创建测试环境
USE tempdb;
IF OBJECT_ID(dbo.Orders) IS NOT NULL
 DROP TABLE dbo.Orders;
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;
-- 行转列的静态方案二：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
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
-- 行转列的动态方案一：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 
IF dbo.fn_CheckSQLInjection(@SQL)=0
SET @SQL=N SELECT custid +@SQL+N  FROM orders group by custid 
PRINT @SQL
EXEC sp_executesql @SQL
-- 行转列的动态方案二：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
-- 列转行的动态方案：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 如何实现行转列和列转行”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！