Grouping ting & ungrouping Ting

The sort ting technology refers to a technology that can convert rows into columns. unsort Ting can be seen as the reverse operation of sort ting, which will rotate columns into rows.

First, make a test table.

1. Create TableFormatting(
   
2. OrderidInt Not Null,
3. OrderdateDate Not Null,
4. EmpidInt Not Null,
5. Custid vahrchar(10) Not Null,
6. QtyInt Not Null,
7. Primary Key(Orderid,Orderdate));

Insert some test data:

1. Insert IntoFormattingSelect1,'2017-01-02',3,'A',10;
   
2. Insert IntoFormattingSelect2,'2017-04-02',2,'B',20;
3. Insert IntoFormattingSelect3,'2017-2010',1,'A',30;
4. Insert IntoFormattingSelect4,'2017-07-02',3,'D',40;
5. Insert IntoFormattingSelect5,'2017-01-02',4,'A',20;
6. Insert IntoFormattingSelect6,'2017-01-02',3,'B',30;
7. Insert IntoFormattingSelect7,'2017-01-02',1,'C',40;
8. Insert IntoFormattingSelect8,'2017-01-02',2,'A',10;
9. Insert IntoFormattingSelect9,'2017-01-02',3,'B',20;

The formatting table can be viewed as a summary table, such as the shopping details of the online store.
This summary table shows the order number, order date, employee number, consumer number, and order quantity.

To further count the order quantity of each consumer each year based on this summary table, you may think of using groups to obtain results, such:

 

1. 
   SelectCustid,Year(Orderdate) Year,Sum(Qty) Sum
   
2. FromFormatting
3. Group by custid,Year(Orderdate);

Result:


This is not intuitive. If you can get this result through rotation, it will be more intuitive and clear:

1. SelectCustid,
   
2. Ifnull(Sum(Case WhenA=2009ThenQtyEnd),0)\"2009 \",
3. Ifnull(Sum(Case WhenA=2010ThenQtyEnd),0)\"2010 \",
4. Ifnull(Sum(Case WhenA=2011ThenQtyEnd),0)\"2011 \"
5. From (SelectCustid,Year(Orderdate)A,Qty
6. FromFormatting)P
7. Group by custid;

 

However, when many rotating elements speak, a long query string is generated.
To shorten the query string, a matrix table can be created in advance, containing the attributes of each column to be rotated:

1. Create TableFormatting_temp(
   
2. OrderyearIntPrimary Key,
3. Y2009Int,
4. Y2010Int,
5. Y2011Int);

 

1. Insert IntoFormatting_tempSelect2009,1,0,0;
   
2. Insert IntoFormatting_tempSelect2010,0,1,0;
3. Insert IntoFormatting_tempSelect2011,0,0,1;

Connect formatting and formatting_temp

1. SelectCustid,
   
2. Sum(Qty*Y2009) "2009",
3. Sum(Qty*Y2010) "2010",
4. Sum(Qty*Y2011) "2011"
5. From(SelectCustid,Year(Orderdate)A,Qty
6. FromFormatting)O
7. Join formatting_temp P
8. On o.A=P.Orderyear
9. Group by custid;

 

 

 

 

[Unmarshting]

 

The unmarshting operation is to convert columns into rows, which is the inverse operation of grouping ting.

1. Create TableUnmarshting(
   
2. Custid vahrchar(10),
3. Y2009Int,
4. Y2010Int,
5. Y2011Int);

1. Insert IntoUnmarshting
   
2. SelectCustid,
3. Ifnull(Sum(Case WhenA=2009ThenQtyEnd),0)\"2009 \",
4. Ifnull(Sum(Case WhenA=2010ThenQtyEnd),0)\"2010 \",
5. Ifnull(Sum(Case WhenA=2011ThenQtyEnd),0)\"2011 \"
6. From (SelectCustid,Year(Orderdate)A,Qty
7. FromFormatting)P
8. Group by custid;

Here we import the content after the previous formatting rotation to the unmarshting table.

 

Currently, the content of the unsorted ting table



Solutions and ideas:
To solve this problem, you need to rotate the column into a row. The trick here is to generate three copies of each row of data.
Each copy generates a column to be rotated. This process can be completed through the following cross join:

1. Select * 
   
2. FromUnmarshting,(Select2009AsOrderyear
3. Union allSelect2010
4. Union allSelect2011)A;

Result:





Next, you only need to follow the corresponding rotation column value of the orderyear column.

As you finally want to get




Filter out qty = 0


Therefore, the final solution to this problem is:

1. SelectCustid,Orderyear,Qty
   
2. From(
3. SelectCustid,Orderyear
4. CaseOrderyear
5. When2009ThenY2009
6. When2010ThenY2010
7. When2011ThenY2011
8. End AsQty
9. FromUnmarshting,(Select2009AsOrderyear
10. Union allSelect2010
11. Union allSelect2011)A
12. )B
13. WhereQty<>0;

 

Grouping ting & ungrouping Ting