Shulou(Shulou.com)05/31 Report--

This article is to share with you about how SQL implements row and column wrapping. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The transformation of ranks and ranks is a demand that is often encountered. The implementation methods are case when mode and built-in pivot and unpivot methods after 2005.

After reading the section on Technical Insider, although these solutions have been used a long time ago, there is no systematic understanding and summary. In order to deepen our understanding, let's sum up again.

Column-column rotation can be divided into static rotation, that is, knowing in advance how many rows (columns) to be processed; dynamic conversion, not knowing how many rows (columns) to be processed in advance.

-- create a test environment USE tempdb;GOIF OBJECT_ID ('dbo.Orders') IS NOT NULL DROP TABLE dbo.Orders;GOCREATE 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,' Cure, 14); INSERT INTO dbo.Orders (orderid, orderdate, empid, custid, qty) VALUES (20001, '20030212, 2,' Bamboo, 12); INSERT INTO dbo.Orders (orderid, orderdate, empid, custid, qty) VALUES (40005, '20040212, 4,' Aids, 10) INSERT INTO dbo.Orders (orderid, orderdate, empid, custid, qty) VALUES (20002, '20040216, 2,' Cure, 20); INSERT INTO dbo.Orders (orderid, orderdate, empid, custid, qty) VALUES (30003, '20040418, 3,' Bamboo, 15); INSERT INTO dbo.Orders (orderid, orderdate, empid, custid, qty) VALUES (30004, '20020418, 3,' Cure, 22) INSERT INTO dbo.Orders (orderid, orderdate, empid, custid, qty) VALUES (30007, '20020907, 3,' Downs, 30); GO

Row-to-column-static scheme:

Static scheme 1: CASE WHEN, compatible with sql2000select 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 ordersgroup by custid Static scheme 2 for GO-- row conversion: PIVOT,sql2005 and later versions of select * from (select custid,YEAR (orderdate) as years,qty from orders) as ordpivot (sum (qty) for years in ([2002], [2003], [2004])) as pGO

Row transfer-dynamic scheme: adding xml processing and SQL injection prevention judgment

Now that you're using dynamic SQL, there's an old topic: SQL injection. Build a judgment function for injecting characters. CREATE FUNCTION [dbo]. [fn_CheckSQLInjection] (@ Col nvarchar (4000)) RETURNS BIT-returns true if there are possible injection characters, and falseASBEGINDECLARE @ result bit; IF UPPER (@ Col) LIKE UPPER (Null% 0x%') OR UPPER (@ Col) LIKE UPPER (Native%) if there are possible injection characters %') OR UPPER (@ Col) LIKE UPPER (Nintendo% exec%') OR UPPER (@ Col) LIKE UPPER (Nasty% aforesaid%') OR UPPER (@ Col) LIKE UPPER (Nude% debacle% exec%') OR UPPER (@ Col) LIKE UPPER (nasty% exec%') OR UPPER (@ Col) LIKE UPPER (Nasty% exec%') OR UPPER (@ Col) LIKE UPPER (Nasty% spares%') OR UPPER (@ Col) LIKE UPPER (N'%SELECT) %') OR UPPER (@ Col) LIKE UPPER (Null% insert%') OR UPPER (@ Col) LIKE UPPER (Nude% update%') OR UPPER (@ Col) LIKE UPPER (Nude% delete%') OR UPPER (@ Col) LIKE UPPER (Null% TRUNCATE%') OR UPPER (@ Col) LIKE UPPER (Null% create%') OR UPPER (@ Col) LIKE UPPER (Null% alter%') OR UPPER (@ Col) LIKE UPPER (Nude% drop%') SET @ result=1 ELSE SET @ result=0 return @ resultENDGO-- dynamic scenario 1: CASE WHEN Compatible with sql2000DECLARE @ 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) = nonexistent where @ Y IS NOT NULLBEGIN 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) ENDIF dbo.fn_CheckSQLInjection (@ SQL) = 0SET @ SQL=N'SELECT custid'+@SQL+N' FROM orders group by custid'PRINT @ SQLEXEC sp_executesql @ SQLGO-- dynamic scenario 2: PIVOT,sql2005 and later versions of 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) = nasty' -- xml processing is used here to process the class group string SET @ SQL=STUFF ((SELECT Numeric parallel quotename (years) FROM @ T FOR XML PATH ('')); IF dbo.fn_CheckSQLInjection (@ SQL) = 0SET @ SQL=N'select * from (select DISTINCT custid,YEAR (orderdate) as years,qty from orders) as ordpivot (sum (qty) for years in ('+ @ SQL+N')) as paired property print @ SQL;EXEC SP_EXECUTESQL @ SQL;GO

Column wrapping:

-- static scheme for column row conversion: UNPIVOT,sql2005 and later versions SELECT * FROM dbo.pvtCustOrdersSELECT custid,years,qtyfrom dbo.pvtCustOrdersunpivot (qty for years in ([2002], [2003], [2004])) dynamic scheme for as upGO-- column row conversion: UNPIVOT,sql2005 and later versions-- because the row is dynamic, columns are obtained from the INFORMATION_SCHEMA.COLUMNS view to construct the row, and XML processing is also used. DECLARE @ SQL NVARCHAR (4000) = Numanite set @ SQL=STUFF ((SELECT Numename (COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNSWHERE ORDINAL_POSITION > 1 AND TABLE_NAME='PvtCustOrders'FOR XML PATH ('')) SET @ SQL=N'SELECT custid,years,qty from dbo.pvtCustOrders unpivot (qty for years in ('+ @ SQL+')) as up';PRINT @ SQL;EXEC SP_EXECUTESQL @ SQL;! This is the end of the article on "how to convert rows and columns in SQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.