PIVOT & SUBQUERY

Welcome People, Bots & Algo’s of THE INTERWEBS. This is the Monday Edition of The Analyst.

?

Today we pivot the Subqery results, but first we do a recap on the Logical processing order of the SELECT statement

?

Recap on the Logical processing order of the SELECT statement

?

The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. FROM

1. ON
2. JOIN
3. WHERE
4. GROUP BY
5. WITH CUBE or WITH ROLLUP
6. HAVING
7. SELECT
8. DISTINCT
9. ORDER BY
10. TOP

PIVOT THE SUBQUERY

The standard way to present a queries result is to have the data presented as individual rows.

Using the PIVOT operator, we can rotate those results, so that the results may be shown in columns which by beneficial for adding to a spreadsheet or another analysis platform

Find the Average list price form each product line

This result is still in rows, we want the result to be in columns.

With the product line as the column headers

The source data will come form a subquery that selects the product line and list price from the product table

The Pivot Operator is used to transform the result and aggregate the values to find the average across the original source data.

We are going to pivot all data by the average list price and we will do that for each of our product lines M R S T

SELECT M, R, S, T
FROM (SELECT ProductLine ,ListPrice
                        FROM Production.Product) AS SourceData
PIVOT (AVG(ListPrice) FOR Productline IN (M, R, S, T)) AS PivotTable;        

Let’s make the result clearer and more understandable

SELECT 'Average List Price' AS 'Product Line'
             ,M, R, S, T
FROM (SELECT ProductLine
                         ,ListPrice
                     FROM Production.Product) AS SourceData
PIVOT (AVG(ListPrice) FOR Productline IN (M, R, S, T)) AS PivotTable;