SQL Server – Building CrossTab Queries – PART 2

Hello Folks,

You might have seen in my previous article post “Building CrossTab Queries – PART 1”, which deals with functioning of the PIVOT method. If you want to see it, you can browse the link from here;

Well this article is being mainly based on the two methods, which is being used to transform the rows data into different column attributes:

• Case Expression Method
• Dynamic Crosstab queries

Case Expression Method:

• The CASE expression method initiates with a normal GROUP BY query generating a row for each value in the GROUP BY column.
• Therefore, adding a ROLLUP function to the GROUP BY adds a nice grand totals row to the crosstab.
• The main function of the CASE expression is to filter the data summed by the aggregate function so as to generate the crosstab columns.
• This will be more clear after you see this example:

First, see the table that we are using in the query;

Note: I am assuming here, that you had learnt the functioning of GROUPING (T-SQL); and if you still not aware of GROUPING (T-SQL), then refer it to the link;

Therefore, there shouldn’t be any problem while going through this query, which is generally being based upon the value of GROUPING. It can be seen as:

SELECT CASE GROUPING(LName)
WHEN 0 THEN LName
WHEN 1 THEN 'All Names'
END AS LName,
SUM(CASE WHEN Class = 'High' THEN Salary ELSE 0 END)
AS High,
SUM(CASE WHEN Class = 'Low' THEN Salary ELSE 0 END)
AS Low,
SUM(CASE WHEN Class = 'Medium' THEN Salary ELSE 0 END)
AS Medium,
SUM(Salary) AS Total
FROM Students
GROUP BY RollUp (LName)

The result can be seen as:

There are some advantages of CASE expression over the PIVOT method are:

• The GROUP BY is explicit. There’s no guessing which columns will generate the rows.
• The crosstab columns are defined only once.
• It’s easy to add a grand totals row.

Dynamic Crosstab Queries:

• Here, the rows of a crosstab query are automatically dynamically generated by the aggregation at runtime.
• This feature makes it different from the previous two methods (Pivot Method and Case Expression Method) where the crosstab columns are generally hard-coded.
• The one and only way to create a crosstab query with dynamic columns is to determine the columns at execution time and assemble a dynamic SQL command to execute the crosstab query.
• The following example will make you more clear about this;

I have used the same table as shown above, i.e., “dbo.Students” from database “TEST”.

Here, I have also used multiple-assignment variable SELECT to create the list of Classes in the @SQLStr and a little string manipulation to assemble the PIVOT statement and an sp_executesql command, which accomplishes the job:

DECLARE @SQLStr NVARCHAR(max)
SELECT @SQLStr = COALESCE(@SQLStr + ',', '') + [a].[Column]
FROM (SELECT DISTINCT Class AS [Column]
      FROM Students) AS a
SET @SQLStr = 'SELECT LName, ' + @SQLStr
+ ' FROM (Select LName, Class, Salary from Students) sq '
+ ' PIVOT (Sum (Salary) FOR Class IN ('+ @SQLStr + ')) AS pt'
 
EXEC sp_executesql @SQLStr;

The result can be seen as:

Hence, this was all about for this article post.

In the next post I would like to deal with UNPIVOT method which is again very handy when transforming the column attributes back to the rows.

So keep in touch!

Hope you got it understood well 🙂

And also comments on this!!

Regards

Piyush Bajaj

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook