Clash Royale CLAN TAG#URR8PPP

How to show count of sub elements in Group By query - Pivoted

We have cars table.
It has marques:

We have cities:

Table:

It has all 250 000 cars for the 3 cities.

How do we show them grouped by city and count, but the cities are columns.

This is my query:

SELECT Count(veh.id) [Count],pd.District, vet.Name FROM Vehicles veh

INNER JOIN PostalDistricts pd on pd.Id = veh.PostalDistrictId
INNER JOIN VehicleMarqueId vet on vet.id = veh.VehicleMarqueId

GROUP BY pd.District, vet.Name

ORDER BY Count(veh.id) DESC, pd.District asc


But the result is:

+-------+-------------+--------+
| Count | City | Marque |
+-------+-------------+--------+
| 9547 | New York | Toyota |
| 3509 | Dallas | Toyota |
| 2608 | Los Angeles | Toyota |
| 2545 | New York | Nissan |
| 2107 | Dallas | Nissan |
| 1780 | Los Angeles | Nissan |
+-------+-------------+--------+


Expected is:

+-------------+--------+--------+
| City | Toyota | Nissan |
+-------------+--------+--------+
| Dallas | 3509 | 2107 |
| Los Angeles | 2608 | 1780 |
| New York | 9547 | 2545 |
+-------------+--------+--------+


1 Answer
1

You can use the pivot statement:

pivot

declare @tmp table ([count] int , City varchar(50), Marque nvarchar(50))

insert into @tmp values
(9547, 'New York', 'Toyota')
,(3509, 'Dallas', 'Toyota')
,(2608, 'Los Angeles', 'Toyota')
,(2545, 'New York', 'Nissan')
,(2107, 'Dallas', 'Nissan')
,(1780, 'Los Angeles', 'Nissan')

select * from @tmp
pivot
(
max([count])
for Marque in ([Toyota], [Nissan] )
) piv


Results:

But if you have more values in the Marque column you have to use dynamic TSQL to generate all the columns needed

Marque

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.