T-SQL Crosstab count query

Question

If have the following dataset:

... and I want to do a crosstab of sorts, counting the data against specific criteria e.g.:

Colour criteria: String contains "Blue", "Red", "Yellow" or "Green" (not case sensitive)

Type criteria: String contains "Car", "Lorry", or "Bus (not case sensitive)

... and I would like the result to look like the following:

Is there an SQL query that I can run on the original data to produce the result I'm looking for?

Answer 1

You can use CROSS APPLY with conditional aggregation; CROSS APPLY simplifies the generation of the list of colours:

select c.colour,
  sum(case when v.VehicleData like '%Car%' then 1 else 0 end) Car,
  sum(case when v.VehicleData like '%Lorry%' then 1 else 0 end) Lorry,
  sum(case when v.VehicleData like '%Bus%' then 1 else 0 end) Bus
from vehicles v
cross apply (values ('Blue'), ('Red'), ('Yellow'), ('Green')
) AS c(colour)
where v.VehicleData like '%' + c.colour + '%'
group by c.colour

Output:

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

Demo on dbfiddle