SQL row-to-column and column-to-row code details, SQL-to-row

SQL row-to-column and column-to-row code details, SQL-to-row

Switching between rows and columns is a common requirement. Implementation methods include the case when method and the built-in unlock and ununlock methods after 2005.
After reading the technology insider section, although these solutions have been used for a long time, they have not systematically understood and summarized them. In order to deepen our understanding, I would like to summarize it again.
Rows and columns can be divided into static transformations, that is, the number of rows (columns) to be processed is known in advance, and the number of rows (columns) to be processed is unknown in advance ).

-- Create the 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, '000000', 3, 'A', 10); insert into dbo. orders (orderid, orderdate, empid, custid, qty) VALUES (10001, '000000', 1, 'A', 12); insert into dbo. orders (orderid, orderdate, empid, custid, qty) VALUES (10005, '000000', 1, 'B', 20); INSERT INTO dbo. orders (orderid, orderdate, empid, custid, qty) VALUES (40001, '000000', 4, 'A', 40); insert into dbo. orders (orderid, orderdate, empid, custid, qty) VALUES (10006, '000000', 1, 'C', 14); insert into dbo. orders (orderid, orderdate, empid, custid, qty) VALUES (20001, '000000', 2, 'B', 12); INSERT INTO dbo. orders (orderid, orderdate, empid, custid, qty) VALUES (40005, '000000', 4, 'A', 10); insert into dbo. orders (orderid, orderdate, empid, custid, qty) VALUES (20002, '000000', 2, 'C', 20); insert into dbo. orders (orderid, orderdate, empid, custid, qty) VALUES (30003, '000000', 3, 'B', 15); INSERT INTO dbo. orders (orderid, orderdate, empid, custid, qty) VALUES (30004, '000000', 3, 'C', 22); insert into dbo. orders (orderid, orderdate, empid, custid, qty) VALUES (30007, '123', 3, 'D', 30); GO

Row-to-column-static solution:

-- Static Solution 1 for Row-to-column conversion: 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; GO -- Static scheme 2 for Row-to-column conversion: select * from (select custid, YEAR (orderdate) as years, qty from orders) as ordpivot (sum (qty) for years in ([2002], [2003], [2004]) as pGO

Row-to-column-Dynamic Solution: xml processing and SQL Injection prevention and judgment are added

-- Since dynamic SQL is used, there is an old topic: SQL Injection. Create a judgment function for injecting characters. Create function [dbo]. [fn_CheckSQLInjection] (@ Col nvarchar (4000) returns bit -- RETURNS true IF possible injection characters exist, and falseASBEGINDECLARE @ 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 @ resultENDGO -- Dynamic Solution for Row-to-column conversion 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) = n''; WHILE @ 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) = 0 SET @ SQL = n' SELECT custid' + @ SQL + N' FROM orders group by custid' PRINT @ SQLEXEC sp_executesql @ SQLGO -- Dynamic Solution for Row-to-column conversion II: explain, sql2005 and later versions 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 processing is used to process the class group string 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 ordpivot (sum (qty) for years in ('+ @ SQL + N') as P'; PRINT @ SQL; EXEC SP_EXECUTESQL @ SQL; GO

Column to row:

-- Static scheme for column-to-row conversion: unregister, sql2005, and later SELECT * FROM dbo. pvtCustOrdersSELECT custid, years, qtyfrom dbo. pvtCustOrdersunpivot (qty for years in ([2002], [2003], [2004]) as upGO -- Dynamic Solution for column-to-row: unmerged, sql2005 and later versions -- because the rows are dynamic, we can obtain columns from the INFORMATION_SCHEMA.COLUMNS view to construct rows. XML processing is also used. DECLARE @ SQL nvarchar (4000) = n'; SET @ SQL = STUFF (SELECT n', '+ QUOTENAME (COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNSWHERE ORDINAL_POSITION> 1 AND TABLE_NAME = 'pvtcustorders 'for xml path (''), n') SET @ SQL = n' SELECT custid, years, qty from dbo. pvtCustOrders unordered (qty for years in ('+ @ SQL +') as up '; PRINT @ SQL; EXEC SP_EXECUTESQL @ SQL;

Summary

The above is all the details about SQL row-to-column and column-to-row code in this article. I hope it will be helpful to you. Interested friends can continue to refer to this site: difference between FIND_IN_SET () and IN Mysql, uncertainty of float under sqlserver, MYSQL subquery and nested query optimization instance parsing, etc, if you have any questions, you can leave a message at any time. The editor will reply to you in a timely manner. Thank you for your support for the website!