Understanding T-SQL Crosstab Count Queries

Overview and Background

In this article, we’ll explore how to create a crosstab count query using T-SQL. We’ll delve into the world of conditional aggregation, CROSS APPLY, and GROUP BY clauses to help you generate the desired output.

First, let’s understand what a crosstab table is. A crosstab table is a type of data visualization that displays data in a grid format, where each row represents a unique value from one column (in our case, “Colour”) and each column represents a unique value from another column (e.g., “Car”, “Lorry”, or “Bus”).

To create this type of query, we’ll use the CROSS APPLY operator with conditional aggregation. This will allow us to simplify the generation of the list of colours and then group the results by colour.

The Challenge

Given the following dataset:

we want to create a crosstab count query that meets the following criteria:

• Colour criteria: String contains “Blue”, “Red”, “Yellow” or “Green” (not case sensitive)
• Type criteria: String contains “Car”, “Lorry”, or “Bus” (not case sensitive)

We’d like the result to look something like this:

The Solution

To solve this problem, we’ll use the following T-SQL query:

SELECT 
  c.colour,
  SUM(CASE WHEN v.VehicleData LIKE '%Car%' THEN 1 ELSE 0 END) AS Car,
  SUM(CASE WHEN v.VehicleData LIKE '%Lorry%' THEN 1 ELSE 0 END) AS Lorry,
  SUM(CASE WHEN v.VehicleData LIKE '%Bus%' THEN 1 ELSE 0 END) AS Bus
FROM 
  vehicles v
  CROSS APPLY (VALUES ('Blue'), ('Red'), ('Yellow'), ('Green')) AS c(colour)
WHERE 
  v.VehicleData LIKE '%' + c.colour + '%'
GROUP BY 
  c.colour

How the Query Works

Let’s break down the query step by step:

1. CROSS APPLY: This operator allows us to generate a list of colours dynamically using the VALUES clause.
2. Conditional Aggregation: We use CASE WHEN statements within the SUM function to count the number of occurrences for each type (Car, Lorry, Bus) when the VehicleData contains that colour.
3. WHERE Clause: We filter the results to only include rows where the VehicleData LIKEs the colour plus a percentage sign (%).
4. GROUP BY: Finally, we group the results by Colour.

Explanation and Examples

Let’s consider an example to illustrate how this query works:

Suppose our dataset contains the following records:

Here’s what happens when we run the query:

1. CROSS APPLY: The VALUES clause generates a list of colours: (‘Blue’, ‘Red’, ‘Yellow’, ‘Green’).
2. Conditional Aggregation: For each colour, we count the number of occurrences for each type:
   • Blue: Car (1), Lorry (0), Bus (0)
   • Red: Car (0), Lorry (1), Bus (1)
   • Yellow: Car (0), Lorry (0), Bus (1)
   • Green: Car (1), Lorry (0), Bus (0)
3. WHERE Clause: We filter the results to only include rows where the VehicleData LIKEs the colour plus a percentage sign (%):
   • Blue: Blue Car | Red Lorry | Yellow Bus | Green Car
   • Red: Red Lorry | Red Bus
4. GROUP BY: Finally, we group the results by Colour and calculate the sum of each type:

Demo and Conclusion

You can test the query on your own dataset using a tool like dbfiddle. Here’s an example of what the output might look like:

colour Car Lorry Bus Blue 3 1 0 Red 1 2 1 Yellow 0 0 1 Green 1 0 0

In conclusion, this T-SQL query uses CROSS APPLY with conditional aggregation to create a crosstab count query that meets the desired criteria. By breaking down the problem step by step and using meaningful variables, we can generate the desired output.

Additional Tips and Variations

• To handle case sensitivity issues, you can use the LOWER or UPPER function when comparing strings.
• If you need to handle additional columns or types, simply add more CASE WHEN statements to your aggregation functions.
• For larger datasets, consider using a data warehousing solution like SQL Server Analysis Services (SSAS) for more efficient query performance.

Troubleshooting

If the query isn’t producing the desired results, try:

• Verifying that the dataset contains the correct values and formatting
• Checking the query syntax and variable names
• Using a SELECT statement to verify the results before running the full query

Last modified on 2023-06-17