7 Perspective and inverse perspective of SQL Server Technology Insider

1. Perspective transformations

Pivot data (pivoting) is a process of rotating data from the state of a row to a column, where values may need to be aggregated.

Each perspective transformation involves three logical processing stages, each of which has related elements: the grouping stage processes the related grouping or row elements, the extended (spreading) stage processes the associated extension or column elements, and the aggregation stage processes the associated aggregation elements and aggregation functions. The grouping element in the example is empid, the extension element is CustID, the aggregate function is sum (), and the aggregation element is Qty.

(1) Pivot conversion using standard SQL

= == ' d ' then qty END, as D from dbo. Orders GROUP by Empid

(2) using the T-SQL pivot operator for perspective conversion

Select Empid,a,b,c,dfrom (select Empid,custid,qty from         dbo. Orders) as Dpivot (SUM (qty) for CustID in (a,b,c,d)) as P

The pivot operation implicitly the source table (or table expression) neither as an extension element nor as a grouping element, so when using the pivot operator, it is necessary to ensure that the source table of the pivot operator can no longer contain additional properties in addition to grouping, extending, and aggregating elements.

2. Inverse Perspective Conversion

Inverse perspective transformation is a technique that rotates data from the state of a column to the state of a row.

The reverse perspective is now required to transform the data, returning a row of records for each employee and customer combination that contains the order quantity for this combination, and the expected output should look like this:

(1) Reverse Perspective conversion using standard SQL
The standard SQL solution has a very clear implementation of 3 logical processing phases: generating replicas, extracting elements, and deleting unrelated intersections.

... The first step in the solution is to generate multiple copies from each row of the source table, in which case a, B, C, and D 4 columns need to be generated separately

SELECT * from dbo. Empcustorderscross JOIN (VALUES ('A'), ('B'), ('  C'), ('D')) as custs (CustID);

After implementing the first step of the solution, the following output results are returned:

... The second step is to generate a data column that returns the column values corresponding to the customer represented by the current replica. Specifically, for this example, if the value of the current CustID is a, the Qty column should return the value of column A, and if the current CustID value is b, the Qty column should return the value of column B.

After implementing the second step of the solution, the following output results are generated:

... The third step is to remove the irrelevant cross

(2) Inverse perspective conversion using T-SQL Unpivot operator

SELECT empid,custid,qtyfrom dbo. Empcustordersunpivot (Qty for CustID in (a,b,c,d)) as U;　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　

7 Perspective and inverse perspective of SQL Server Technology Insider