MySQL column inversion pivoting

pivoting is a technique that can rotate rows into columns. Aggregations may be used during the execution of pivoting. Pivoting technology is widely used. The following is a static pivoting query, in which the user needs to know in advance the values of the rotated properties and columns. For dynamic pivoting, strings need to be constructed dynamically.

Open ArchitectureA development architecture is a design pattern that is used to change the schema frequently. With relational databases and SQL statements, DML can be handled very efficiently, including INSERT, SELECT, update, and delete. However, DDL is very inconvenient when making schema changes frequently. For example, the city modifies the table structure, and the user must add, modify, or delete columns, which is exactly what the relational database is not good at. Therefore, in the case of frequent schema changes, all data can be stored in a table, each row stores the value of an attribute, and is stored with varchar, because it accommodates various types of data. The following statement generates a table T of the development schema.

CREATE TABLE t (ID int,attribute varchar, value varchar), PRIMARY KEY (Id,attribute));

INSERT into T select 1, ' attr1 ', ' BMW '; insert into T select 1, ' attr2 ', ' + '; insert into T select 1, ' Attr3 ', ' 2010-01-01 '; inse RT into T select 2, ' attr2 ', ' n '; insert into T select 2, ' Attr3 ', ' 2010-03-04 '; insert into T select 2, ' Attr4 ', ' M '; Insert INT  O T Select 2, ' ATTR5 ', ' 55.60 '; insert into T select 3, ' attr1 ', ' SUV '; insert into T select 3, ' attr2 ', ' ten '; INSERT INTO T Select 3, ' Attr3 ', ' 2011-11-11 ';

The contents of the table are as shown in the example above, when adding, modifying, or deleting tables and columns from a table designed with an open schema, you only need to complete the changes to the logical schema through the INSERT, UPDATE, delete operations. Of course, using this approach may cause other features of the database to be unusable, such as integrity constraints, SQL optimizations, and querying data that is less straightforward and straightforward than previous SQL statements. Therefore, for tables designed with open architecture, pivoting technology is commonly used to query data. Privoting technology needs to be used with aggregation, first to determine the relationship between the number of rows in the result and the number of rows in the table. For open schema table T, there should be 3 rows and 5 columns, which can be obtained by grouping IDs. Therefore, the following pivoting can be used to get the data.

SELECT ID, max (case when    attribute= ' attr1 "then value END) as ATTR1,    max (if attribute= ' attr2 ' then value EN D) as ATTR2, Max (case when    attribute= ' Attr3 "then value END) as ATTR3,    Max (case when attribute= ' ATTR4 ' then value END) as ATTR4,    MAX (case is attribute= ' ATTR5 ' then value END) as Attr5from tgroup by ID;

Pivoting the number of rows that are recorded after the rows and columns are grouped by ID first. After that, a list of 5 properties is known to determine the number of rows with 5 columns of data, and the value of each column is obtained by case. Because of the use of grouping techniques, it is important to use the grouping function to get the value of the column, so use the Max function here, of course, you can also use the Min function. The final results are as follows

This type of rotation is very efficient because it only scans the table once. In addition, this is a static pivoting, the user must know how many properties, but for the general Open schema table, the user will define a maximum number of attributes, so it is easier to pivoting Relationship DivisionRelational Division (Rational Divistion), like the common relational operations join, SEMI join, is a relational algebra. Pivoting can be used to solve the problem of relationship division when the number of elements in the divide is less than an hour. Start by creating a table T and populating the data.

CREATE TABLE T1 (OrderID VARCHAR () not Null,productid INT not null,primary KEY (Orderid,productid));

INSERT into T1 select ' A ', 1;insert to T1 select ' A ', 2;insert into T1 select ' A ', 3;insert into T1 select ' A ', 4;insert INT O T1 Select ' B ', 2;insert into T1 select ' B ', 3;insert to T1 select ' B ', 4;insert into T1 select ' C ', 3;insert into T1 Selec T ' C ', 4;insert into T1 SELECT ' D ',

The table contains the following table T stores the products contained in the order, such as the products contained in a order contains the IDs 1, 2, 3, 4,b orders included in the products are 2, 3, 4, and so on. This is a more typical relationship division problem. Use pivoting technology to rotate products in the counter order to a separate column. For example, to query for orders that contain ProductID 2, 3, and 4, you can use the following methods:

Select Orderidfrom (select Orderid,max (Case-productid=2 then 1 end) as-P2,max (case, productid=3 then 1 end) as P3 , MAX (case is productid=4 then 1 END) as P4from T1group by OrderID) as PWhere p2=1 and P3=1 and p4=1;

The above statements return "A" and "B". If you run the subquery separately, you will get the product ID for each order, and the results are as follows

For this problem, the aggregate function can use count to replace Max, which makes the result of the derived table more intuitive. If the product exists then returns 1, does not exist then returns 0 instead of NULL, so the SQL statement can be adjusted to

Select Orderidfrom (select Orderid,count (case is productid=2 then 1 end) as P2,count (case when productid=3 then 1 end) A S P3,count (case is productid=4 then 1 END) as P4from T1group by OrderID) as PWhere p2=1 and P3=1 and p4=1;

formatting aggregate functionsPrivoting technology can also be used to format aggregated data, which is typically used for presentation of reports. In order to demonstrate the use of pivoting technology for formatting, here is an example. First by creating a table T2 and populating the data

CREATE TABLE T2 (OrderID int not null,orderdate DATE not null,empid int. not Null,custid VARCHAR (Ten) not Null,qty int. not N Ull,primary KEY (orderid,orderdate));

INSERT into T2 Select 1, ' 2010-01-02 ', ' 3 ', ' A ', 10;insert to T2 select 2, ' 2010-04-02 ', ' 2 ', ' B ', 20;insert into T2 SELECT 3, ' 2 010-05-02 ', ' 1 ', ' A ', 30;insert into T2 select 4, ' 2010-07-02 ', ' 3 ', ' D ', 40;insert into T2 SELECT 5, ' 2011-01-02 ', ' 4 ', ' A ', 20 INSERT into T2 Select 6, ' 2011-01-02 ', ' 3 ', ' B ', 30;insert to T2 Select 7, ' 2011-01-02 ', ' 1 ', ' C ', 40;insert into T2 select 8, ' 2009-01-02 ', ' 2 ', ' A ', 10;insert into T2 SELECT 9, ' 2009-01-02 ', ' 3 ', ' B ', 20;

The contents of the table can be seen as a summary table T2, such as the shopping details of the online mall. This summary table shows the order number, order date, employee number, consumer number, and order quantity. To further count the number of consumers per month on the basis of this summary table, you might think of grouping to get results, such as

SELECT Custid,year (OrderDate) as Year,sum (qty) as Sum_qtyfrom T2 GROUP by Custid,year (OrderDate)

The result of the operation as follows there is no problem, but the display may not be intuitive. If the output can be rotated, it is much more intuitive and clear. Pivoting technology can also be used here. The only difference is that the aggregate function max is no longer used here, and the SUM function is used instead. The SQL statement for this solution is as follows:

SELECT CustID,       ifnull (sum (case-orderyear=2009 then qty-END), 0) as ' SUM ',       ifnull (case when orderyear= Qty END), 0) as ',       ifnull (SUM (case orderyear=2011 then qty END), 0) as ' + ' from       (SELECT custi D,year (OrderDate) as Orderyear,qty from T2) as Pgroup by CustID;

The Ifnull function in the SQL statement above is used to return a null value of 0, which means that the consumer did not produce any order operations for that year. Using the pivoting technique to format aggregated data has the problem of having a longer query string when the elements are rotated very often. To shorten the character length of a query, you can pre-generate a matrix table containing each property to rotate the column, and run the following statement to create and populate the matrix table

CREATE TABLE Matrix (orderyear int PRIMARY key,y2009 int null,y2010 int null,y2011 int NULL);

INSERT into matrix select 2009,1,0,0;insert to Matrix select 2010,0,1,0;insert into matrix select 2011,0,0,1;

Matrix table The contents of the matrices are as follows so that the original: sum (case-orderyear=n then qty END) as n is replaced with sum (Qty*yn) as n by connecting the table T and table Maxtrix to the full SQL query statement:

Select CustID,    sum (qty*y2009) as ',    sum (qty*y2010) as ',    sum (qty*y2011) as ' from (SELECT Custid,year (OrderDate) as Orderyear,qty from T2) as Oinner JOIN Matrix as P in    o.orderyear=p.orderyeargroup by CustID;

The result of the operation is as follows Unpivoting Unpivoting can be seen as a pivoting reverse operation, and the column is rotated into rows. To complete this example, you need to create and populate the table based on the following statementp,

CREATE TABLE P (CustID VARCHAR () not null,y2009 int null,y2010 int null,y2011 int null,primary KEY (CustID));

INSERT into Pselect    CustID,    ifnull (the case is orderyear=2009 then qty END), 0) as ' Sum ',    ifnull (the case When orderyear=2010 then Qty end), 0) as ' $ ',    ifnull (SUM (case is orderyear=2011 then qty end), 0) as ' + ' from (S Elect CustID, Year (OrderDate) as OrderYear, Qtyfrom T2) as Pgroup by CustID;

Here, the contents of the T2 table are imported into the table p, if you want to get the T2 table directly aggregated results, this problem becomes unpivoting problem. To resolve this problem, you need to rotate the column to a row. The technique used here is to produce 3 copies of each row of data, each of which produces a column that needs to be rotated, which can be done through the following cross join.

SELECT * from    p,    (SELECT-OrderYear    UNION ALL SELECT-UNION ALL    Select) as O

The following results can be obtained, and then the problem is simple, just follow the orderyear column to get the corresponding rotation column value, for example:

Case Orderyearwhen Y2009when and then Y2010when and Y2011end as Qty

So the solution to this unpivoting problem is as follows

SELECT Custid,orderyear, Case Orderyearwhen and then Y2009when and Y2010when then y2011end as Qtyfrom    p,
    (select OrderYear UNION ALL    Select-UNION ALL    Select) as O

To get the final result, you also need to filter the case where qty equals 0, so the final solution is:

Select Custid,orderyear,qtyfrom (select Custid,orderyear, Case Orderyearwhen and then Y2009when y2010when 2011 Then y2011end as Qtyfrom    p,    (SELECT-orderyear    UNION ALL SELECT-UNION ALL    Select) as O) A S Mwhere Qty <> 0

MySQL column inversion pivoting