How to make pivot in MS SQL?
In the table with cars model and model year we have the following data.
And I’d like to create a pivot with the names of car models as columns and grouped by car model year and see how many such cars we have from each year.
To get this result at first we need to write query with the creation of columns (car models) separated by a comma.
DECLARE @columns AS NVARCHAR(MAX) = ''; select @columns += STUFF((SELECT DISTINCT ',' + QUOTENAME(car) AS Expr1 FROM Cars FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') ;
Now we can create a query with pivot.
SELECT @query = 'SELECT * FROM ( SELECT id, car, carModelYear FROM Cars ) AS tab PIVOT ( COUNT(id) FOR car IN( ' + @columns + ' )' + ' ) AS pivop ; ' ; execute(@query);
With that way we can create any pivot with any columns.