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;

1_SQL_Server_Building_CrossTab_Queries_PART2

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:

2_SQL_Server_Building_CrossTab_Queries_PART2

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:

3_SQL_Server_Building_CrossTab_Queries_PART2

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

 
Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

One Comment on “SQL Server – Building CrossTab Queries – PART 2”

  1. Thanks for the post. I appreciate the simplicity of your solution. I would suggested modifying the second line of the Dynamic Crosstab query to allow for column names that contain spaces.

    Before:

    SELECT @SQLStr = COALESCE(@SQLStr + ‘,’, ”) + [a].[Column]

    After:

    SELECT @SQLStr = COALESCE(@SQLStr + ‘,’, ”) + ‘[‘ + [a].[Column] + ‘]’

Leave a Reply

Your email address will not be published. Required fields are marked *