Dynamic Cross Tab Query in SQL Server

• Introduction.
• Real world scenario.
• Solution.
• Conclusion.

Introduction

Let us begin by examining what a cross tab query is and when to use it. A cross tab query is a transformation of rows of data into columns. It usually involves aggregation of data. e.g. revenue generated by an employee broken down by days, where the days are represented by columns.
It’s very hard to visualize without an example, so we will look into one below.
SQL Server 2005 and above supports PIVOT statement for building cross tab queries and we will be using the same in this article.

Real world scenario

Suppose we have a table called [EmployeeSales] for recording sales made by employees. For the sake of simplicity we’ll create a table as in Fig 1 below.

Fig 1.

One example of a cross-tab is to take the above data and show how much revenue each employee generated per day. This allows for easy comparison of revenue generated by each employee per day . We would want our resulting data to look like Fig 2 below.

Fig 2.

Solution

SQL Server has introduced many cool features beginning from version 2005 onwards and PIVOT is one such feature which can be used for formatting output as shown above.
Following is the syntax for PIVOT statement in SQL Server.

SELECT
  <-non-pivoted column>,
  [first pivoted column] AS <-column name>,
  [second pivoted column] AS <-column name>,
  ...
  [last pivoted column] AS <-column name>
FROM
  (<-SELECT query that produces the data>)
  AS <-alias for the source query>
PIVOT
(
	  <-aggregation function>(<-column being aggregated>)
	FOR
		[<-column that contains the values 
                 that will become column headers>]
	IN 
	( 
		[first pivoted column], 
		[second pivoted column],
		... [last pivoted column]
	)
) 
AS <-alias for the pivot table>
<-optional ORDER BY clause->;
        

So writing a PIVOT statement for our requirement looks like this

    SELECT
       EmployeeName,
       [03 Apr 2010],
       [04 Apr 2010]
    FROM
        (
            SELECT EmployeeName,
            convert(varchar, SaleDate, 106) AS [Date] ,
            Revenue
            FROM dbo.EmployeeSales
        ) p
    PIVOT
        (
            SUM(Revenue) FOR
            [Date] IN
            (
                [03 Apr 2010],
                [04 Apr 2010]
            )
        )
        AS pvt

        

However PIVOT syntax in the above query requires us to give all the PIVOT column names hard coded in the query which obviously is not a cool thing.

How many times do we know in advance the columns that are to be pivoted?

But wait, If we could find some mechanism to generate all the pivoted column names that should appear in the output dynamically, Wouldn’t that solve our issue?

Of course, Yes.

Good news is that it is not that difficult to generate a comma separated values of row data (in this case, the distinct dates) from the original result set.

We’ll use an approach using COALESCE for this purpose.

Once we generate the comma separated values of distinct dates, we append it to out above PIVOT statement to build the complete query dynamically.

Then we execute the query on the fly.

So here goes our SQL code

-- Getting distinct Dates into a temporary table #Dates
SELECT DISTINCT convert(varchar, SaleDate, 106) AS [Date]
INTO #Dates
FROM dbo.EmployeeSales
ORDER BY [Date]

-- Building a comma separated list of Dates in #Dates
DECLARE @cols varchar(1000)
SELECT @cols = COALESCE 
			( 
				@cols + ',[' + [Date] +']',
				'[' + [Date] + ']'
			)
FROM #Dates

-- Building the query appending columns
DECLARE @qry varchar(4000)
SET @qry =
'SELECT EmployeeName, ' 
		+ @cols 
		+ ' FROM
(
	SELECT EmployeeName, 
	convert(varchar, SaleDate, 106) AS [Date] , 
	Revenue
	FROM dbo.EmployeeSales
) p
PIVOT
(
	SUM(Revenue) FOR
	[Date] IN (' + @cols + ')
)
AS pvt'

-- Executing the query
EXEC(@qry)

-- Dropping temporary table
DROP TABLE #Dates
        

Conclusion

So That’s it. We’ve just created a dynamic cross tab query in SQL Server. As you have just seen, PIVOT statement along with the capability to generate column names dynamically provides some very useful functionality in terms of formatting a result set as per our needs.

Thanks for reading the article.

Do post your Comments, Suggestions and Views.

Have a nice day and happy programming !!!