If you might be searching all around for the query to transform the rows of data to columns and columns of data back to the rows, then you don’t have to worry at all because I am going to deal with these queries in a moment 🙂
What is CrossTab Query?
The Cross-tabulation, or Crosstab, query pivots the second GROUP BY column (or dimension) values counterclockwise 90 degrees and turns it into the crosstab columns.
Although the columnar GROUP BY query can have multiple aggregate functions, a crosstab query has difficulty displaying more than a single measure.
The following are the methods to transform the row data into different column attributes-
- Pivot Method
- Case Expression Method
- Dynamic Crosstab Queries
The method which is being use to transform the column attributes back to the row is by using “Unpivot Method”. We will deal with each one of them separately:
- Microsoft have the introduced this keyword with the release of SQL Server 2005, which is being used for coding crosstab queries.
- The pivot method deviates from the normal logical query flow by performing the aggregate GROUP BY function and generating the crosstab results as a data source within the FROM clause.
- If you think of PIVOT as a table-valued function that’s used as a data source, then it accepts two parameters. The first parameter is the aggregate function for the crosstab’s values. The second measure parameter lists the pivoted columns.
- This method will be more clear to you, of you carefully see this example:
First, see the table that we are using in the query;
So, as per the definition of Cross-Tab query, I will pivot the second column values counterclockwise (90 degrees) and turns it into the crosstab columns.
Here, the aggregate function sums the “Salary” column, and the pivoted columns are the “Class”. Because PIVOT is part of the FROM clause, the data set needs a named range or table alias as “pt”. So you can see the query as:
SELECT LName,High,Low,Medium FROM (SELECT LName,Class,Salary From Students) sq PIVOT (SUM(Salary) FOR Class IN (High, Low, Medium) ) AS pt
The result-set can be seen as:
So, you can observe that the “Class” column is being transformed to different attribute classes. And the “LName” field which does not match the “Class” attribute is being replaced with the NULL.
Well this was all about the PIVOT method, and is enough for the PART 1.
In the next article post I will be writing about Case Expression Method, Dynamic Crosstab queries and Unpivot method.
So be in touch!
Hope you got it understood well 🙂
And also comments on this!!