主頁 > 知識庫 > SQL行轉(zhuǎn)列和列轉(zhuǎn)行代碼詳解

SQL行轉(zhuǎn)列和列轉(zhuǎn)行代碼詳解

行列互轉(zhuǎn)，是一個經(jīng)常遇到的需求。實現(xiàn)的方法，有case when方式和2005之后的內(nèi)置pivot和unpivot方法來實現(xiàn)。
在讀了技術(shù)內(nèi)幕那一節(jié)后，雖說這些解決方案早就用過了，卻沒有系統(tǒng)性的認(rèn)識和總結(jié)過。為了加深認(rèn)識，再總結(jié)一次。
行列互轉(zhuǎn)，可以分為靜態(tài)互轉(zhuǎn)，即事先就知道要處理多少行（列）;動態(tài)互轉(zhuǎn),事先不知道處理多少行(列)。

--創(chuàng)建測試環(huán)境
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

行轉(zhuǎn)列-靜態(tài)方案：

--行轉(zhuǎn)列的靜態(tài)方案一：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
--行轉(zhuǎn)列的靜態(tài)方案二：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

行轉(zhuǎn)列-動態(tài)方案：加入了xml處理和SQL注入預(yù)防判斷

--既然是用到了動態(tài)SQL,就有一個老話題:SQL注入。建一個注入性字符的判斷函數(shù)。
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
--行轉(zhuǎn)列的動態(tài)方案一：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
--行轉(zhuǎn)列的動態(tài)方案二：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

列轉(zhuǎn)行：

--列轉(zhuǎn)行的靜態(tài)方案：UNPIVOT,sql2005及以后版本
SELECT * FROM dbo.pvtCustOrders
SELECT custid,years,qty
from dbo.pvtCustOrders
unpivot(qty for years in([2002],[2003],[2004]))as up
GO
--列轉(zhuǎn)行的動態(tài)方案：UNPIVOT,sql2005及以后版本
--因為行是動態(tài)所以這里就從INFORMATION_SCHEMA.COLUMNS視圖中獲取列來構(gòu)造行，同樣也使用了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;

總結(jié)

以上就是本文關(guān)于SQL行轉(zhuǎn)列和列轉(zhuǎn)行代碼詳解的全部內(nèi)容，希望對大家有所幫助。感興趣的朋友可以繼續(xù)參閱本站：Mysql中FIND_IN_SET()和IN區(qū)別簡析、淺談sqlserver下float的不確定性、MYSQL子查詢和嵌套查詢優(yōu)化實例解析等，有什么問題可以隨時留言，小編會及時回復(fù)大家的。感謝朋友們對腳本之家網(wǎng)站的支持！

您可能感興趣的文章:

mysql 行轉(zhuǎn)列和列轉(zhuǎn)行實例詳解
sql語句實現(xiàn)行轉(zhuǎn)列的3種方法實例
SQLServer行轉(zhuǎn)列實現(xiàn)思路記錄
MySQL存儲過程中使用動態(tài)行轉(zhuǎn)列
mssql 數(shù)據(jù)庫表行轉(zhuǎn)列，列轉(zhuǎn)行終極方案
Sql Server 2000 行轉(zhuǎn)列的實現(xiàn)（橫排）
SQL查詢語句行轉(zhuǎn)列橫向顯示實例解析
sql動態(tài)行轉(zhuǎn)列的兩種方法
table 行轉(zhuǎn)列的sql詳解
SQL基礎(chǔ)教程之行轉(zhuǎn)列Pivot函數(shù)

標(biāo)簽：蘭州湖南仙桃黃山衡水銅川湘潭崇左

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《SQL行轉(zhuǎn)列和列轉(zhuǎn)行代碼詳解》，本文關(guān)鍵詞；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。