Pivot in MS SQL

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.

Like this:


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 *
        SELECT        id, car, carModelYear
        FROM            Cars
    ) AS tab
        FOR car IN( ' + @columns + ' )' +
    ' ) AS pivop ; ' ;

With that way we can create any pivot with any columns.

Leave a Reply

Your email address will not be published. Required fields are marked *