共計 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 如何實現行轉列和列轉行”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！