SQL row-to-column and column-changing careers

SQL row-to-column and column-changing careers

The ranks of each other, is a frequently encountered demand. The implementation method is implemented with case-when mode and a built-in pivot and Unpivot method after 2005.

After reading the technical Insider's section, although these solutions have long been used, but there is no systematic understanding and summed up. In order to deepen the understanding, summarize once more.

Rows and columns can be divided into static mutual transfer , that is to know beforehand how many rows to process (column),dynamic Mutual transfer , do not know in advance how many rows (columns).

 1--Create test environment 2 use tempdb; 3 GO 4 5 IF object_id (' dbo. Orders ') is not NULL 6 DROP TABLE dbo. Orders; 7 GO 8 9 CREATE TABLE dbo. ORDERS10 (OrderID int not NULL PRIMARY KEY nonclustered,12 orderdate datetime not null,13 Empid in T null,14 CustID varchar (5) Not null,15 qty int not NULL16); CREATE UNIQUE CLUSTERED INDEX IDX_ORDERDATE_ORDERID19 on dbo. Orders (OrderDate, OrderID); INSERT into dbo. Orders (OrderID, OrderDate, Empid, CustID, qty) of VALUES (30001, ' 20020802 ', 3, ' A ', ten); Orders (OrderID, OrderDate, Empid, CustID, qty) VALUES (10001, ' 20021224 ', 1, ' A ', +), INSERT into dbo. Orders (OrderID, OrderDate, Empid, CustID, qty) VALUES (10005, ' 20021224 ', 1, ' B ', +), INSERT into dbo. Orders (OrderID, OrderDate, Empid, CustID, qty) VALUES (40001, ' 20030109 ', 4, ' A ', +), INSERT into dbo. Orders (OrderID, OrderDate, Empid, CustID, qty) VALUES (10006, ' 20030118 ', 1, ' C ', +); INSERT into dbo. Orders (OrderID, OrderDate, Empid, CustID, qty) VALUES (20001, ' 20030212 ', 2, ' B ', N), and INSERT into dbo. Orders (OrderID, OrderDate, Empid, CustID, qty) VALUES (40005, ' 20040212 ', 4, ' A ', ten), and INSERT into dbo. Orders (OrderID, OrderDate, Empid, CustID, qty) VALUES (20002, ' 20040216 ', 2, ' C ', +), PNS INSERT into dbo. Orders (OrderID, OrderDate, Empid, CustID, qty) the VALUES (30003, ' 20040418 ', 3, ' B ', and *); Orders (OrderID, OrderDate, Empid, CustID, qty) all VALUES (30004, ' 20020418 ', 3, ' C ', +), INSERT into dbo. Orders (OrderID, OrderDate, Empid, CustID, qty) in the VALUES (30007, ' 20020907 ', 3, ' D ', +);

Row to column-static scenario:

1--The static scheme of row to column one: case when, compatible with sql2000 2 select CustID, 3 sum (case if year (OrderDate) =2002 then qty end) As [2002], 4 sum (CAS E when year (OrderDate) =2003 then qty end) As [2003], 5 sum (case if year (OrderDate) =2004 then qty end) As [2004] 6 from O Rders 7 Group by CustID; 8 Go 9--the static scheme of row to column two: Pivot,sql2005 and later version of the Select *11 from (select Custid,year (OrderDate) as Years,qty from orders) as Ord12 Pivot (SUM (qty) for Years in ([2002],[2003],[2004])) as P13 GO

row to column- dynamic scenario: Added XML processing and SQL injection prevention judgment

 1-Since the use of dynamic SQL, there is an old topic: SQL injection. Build a judgment function for the injected character. 2 CREATE FUNCTION [dbo]. [Fn_checksqlinjection] 3 (4 @Col nvarchar (4000) 5) 6 RETURNS BIT--if there is possible injection character returns true, Reverse returns false 7 as 8 BEGIN 9 DECLARE @ Result bit;10 IF one 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 up PER (n '%exec% ') or UPPER (@Col) like UPPER (n '%xp_% ')/UPPER (@Col) like UPPER (n '%sp_% ') + or UPPER (@Col) like UPP ER (n '%select% ') or UPPER (@Col) like UPPER (n '%insert% ') + or UPPER (@Col) like UPPER (n '%update% ') in or UPPER (@Col) L IKE UPPER (n '%delete% ') or UPPER (@Col) like UPPER (n '%truncate% '), or UPPER (@Col) like UPPER (n '%create% ') or Uppe R (@Col) like UPPER (n '%alter% '), OR UPPER (@Col) like UPPER (n '%drop% '), set @result =128 ELSE29 set @result =030 ret Urn @result31 END32 GO33 34--dynamic scenario one for row to column: case, compatible sql200035 DECLARE @T TABLE (Years INT not NULL PRIMARY KEY); n INSERT into @T Notoginseng SELECT DISTINCT Year (OrderDate) from orders;38 DECLARE @y int;39 Set @y= (SELECT MIN (years) from @T), DECLARE @SQL NVARCHAR (4000) =n ";" While @y was not NULL42 BEGIN43 SET @[email&nbsp ;p rotected]+n ', sum (case if year (orderdate) = ' +cast (@y as NVARCHAR (4)) +n ' then Qty End ' as ' +quotename (@y), SET @y= (S) Elect MIN (years) from @T where years>@y), END46 IF dbo.fn_checksqlinjection (@SQL) =047 SET @SQL =n ' SELECT custid ' [Emai L protected]+n ' from Orders group by CustID ' PRINT @SQL49 EXEC sp_executesql @SQL50 GO51 52--dynamic scheme for row to column two: PIVOT,SQL2 005 and later versions of DECLARE @T TABLE (years INT not NULL PRIMARY KEY); Si INSERT into @T-SELECT DISTINCT Year (OrderDate) from Ord ers;56 DECLARE @y int;57 SET @y= (SELECT MIN (years) from @T), DECLARE @SQL NVARCHAR (4000) =n '; 59 60--This is where the XML processing is used to process class groups The string =stuff SET @SQL ((SELECT N ', ' +quotename (years) from @T62 for XML PATH (")), 1,1,n"), and IF dbo.fn_checksqlinjection (@ SQL) =064 SET @SQL =n ' SeleCT * FROM (select DISTINCT custid,year (OrderDate) as Years,qty from orders) as Ord65 pivot (SUM (qty) for Years in (' [EMAIL&N Bsp;protected]+n ') as P '; PRINT @SQL; sp_executesql EXEC @SQL;

List of career change:

1--The static scheme of the column changing: unpivot,sql2005 and later version 2 select * FROM Dbo.pvtcustorders 3 select Custid,years,qty 4 from dbo.pvtcustorders 5 u Npivot (Qty for years on ([2002],[2003],[2004]) as up 6 GO 7--A dynamic scenario for a career change: unpivot,sql2005 and later version 8--Because the row is dynamic so here's the Information_ SCHEMA. The columns view gets the columns to construct the rows, and the XML processing is also used. 9 DECLARE @SQL NVARCHAR (4000) =n "; SET @SQL =stuff ((SELECT N ', ' +quotename (column_name) from INFORMATION_SCHEMA. COLUMNS11 WHERE ordinal_position>1 and Table_name= ' pvtcustorders ' for XML PATH (")), 1,1,n ') SET @SQL =n ' SELECT Custid,years,qty14 from          dbo.pvtcustorders15          Unpivot (Qty to years in (' [email protected]+ ')] as up '; sql;17 EXEC sp_executesql @SQL;

SQL row-to-column and column-changing careers