Dynamic Crosstab with multiple PIVOT columns – Part 2

In Part 1 of this post, I showed a method of producing crosstab results using two PIVOT columns. In this post, I will show you the method of how you can change the grouping columns dynamically.

I am using the same data set created in Part 1. In Part 1, the grouping column was Last name of the employee. Basically it showed the total orders handled by each employee. Now in this post, we will see how to change Last name into some other column.

Let us use Shipping company’s name as grouping column. Executing the following code

EXEC CrossTab_multiple_columns
'SELECT Shipname, OrderDate,shipcity FROM Employees Employees
INNER JOIN Orders Orders ON (Employees.EmployeeID=Orders.EmployeeID) ',
'shipcity,Year(OrderDate)',
'Count(Shipname)[]',
'Shipname'

will show you the following result

pivot3

Let us use Shipping Postal code as grouping column. Executing the following code


EXEC CrossTab_multiple_columns
'SELECT Shipname, OrderDate,shippostalcode FROM Employees Employees
INNER JOIN Orders Orders ON (Employees.EmployeeID=Orders.EmployeeID) ',
'shippostalcode,Year(OrderDate)',
'Count(Shipname)[]',
'Shipname'

will show you the following result

pivot4

Now, as you see, this stored procedure is very dynamic and will work for any dataset (any tables, views, etc). All you need to do is effectively use this procedure and pass the relevant names for aggregation , grouping and PIVOT columns

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s