Note-microsoft SQL Server 2008 Tech Insider: T-SQL Language Basics-07 perspective, inverse perspective, and grouping set

Perspective transformations

Pivot data is a process of rotating data from the state of a row into a column state. Each perspective transformation involves grouping, extending, and aggregating three logical processing stages, each of which has related elements: the grouping stage processes related grouping or row elements, the extension stage processes related extensions or column elements, and the aggregation phase processes related aggregation elements and aggregation functions. Now suppose you have a table with the following data:

I now need to query the following results:

Requirement Analysis: A row of records needs to be generated for each employee in the results, which requires that the rows in the Orders table be grouped according to their empid columns, and, as a result, a different result column is generated for each customer, so the extension element is the CustID column Finally, the data needs to be aggregated (in this case, sum). The following code is a perspective transformation using standard SQL:

SELECTEmpid,SUM( Case  whenCustID= 'A'  ThenQtyEND) asA,SUM( Case  whenCustID= 'B'  ThenQtyEND) asB,SUM( Case  whenCustID= 'C'  ThenQtyEND) asC,SUM( Case  whenCustID= 'D'  ThenQtyEND) asD fromdbo. OrdersGROUP  byEmpid

Use the T-SQL pivot operator for perspective transformations. SQL Server 2005 introduces a T-SQL-specific table operator Pivot,pivot operator that also involves three logical processing stages (grouping, scaling, and aggregation). Note that instead of applying the pivot operator directly to the source table, you apply it to a table expression that contains only the 3 elements required by the perspective transformation and does not contain other properties:

SELECT Empid, A, B, C, D  from (SELECT  empid, CustID, qty      from as  D  PIVOT (SUM   for in as P;

The pivot operator in the preceding code does not directly manipulate the Orders table, but instead operates on a derived table named D, which contains only the pivot transformation elements empid, CustID, Qty.

Inverse Perspective transformation

The requirements are as follows:

Now you need to get this data:

Use standard SQL for inverse perspective transformations. The standard SQL solution for Inverse perspective transformation is very clear to implement 3 logical processing phases: generating replicas, extracting elements, and deleting unrelated intersections.

SELECTEmpid, CustID, CaseCustID when 'A'  ThenA when 'B'  ThenB when 'C'  ThenC when 'D'  ThenDEND  asQty fromdbo. Empcustorders Cross JOIN(VALUES('A'),('B'),('C'),('D')) asCusts (CustID);

The results of the implementation are as follows:

If you want to further filter out data that contains null values, you can:

SELECT * from(SELECTEmpid, CustID, CaseCustID when 'A'  ThenA when 'B'  ThenB when 'C'  ThenC when 'D'  ThenDEND  asQty fromdbo. Empcustorders Cross JOIN(VALUES('A'),('B'),('C'),('D')) asCusts (CustID)) asDWHEREQty is  not NULL;

Use the T-SQL Unpivot operator for inverse perspective transformations:

SELECT empid, CustID, qty  from dbo. Empcustorders  for in as U;

Grouping sets

GROUPING sets subordinate clauses:

SELECT SUM  as Sumqty  from dbo. OrdersGROUPby  GROUPING  sets  (    (Empid, CustID), (    empid),    (CustID),    ()  );

Cube subordinate clauses

SELECT SUM  as Sumqty  from dbo. OrdersGROUP by CUBE (Empid, CustID);

Notes-microsoft SQL Server 2008 Tech Insider: T-SQL Language Basics-07 perspective, inverse perspective, and grouping set