The Pivot operator turns the unique value from one column and transfer the same to multple columns in the output, effectively rotating the table.

Let’s take an example below table which contains batsman stats for the tournaments scores based on date of match as shown below:

In order to get the total runs scored by the Player in the tournament, we can do the group by on the table as shown below:

select firstname+' '+secondname PlayerName,tournamentName,sum(total_Score) 'Total Score'
from  [SampleDb].[dbo].[BatsmanStats]
group by firstname,secondname,tournamentName
order by firstname,secondname,tournamentName

What if our business wants to get player stats as playername and than tournament names which is actually like converting uniquer Tournament Name into multiple columns with the help of Pivot.

syntax of Pivot as per docs.microsoft.com

SELECT non-pivoted column,  
    [first pivoted column] AS column name,  
    [second pivoted column] AS column name,  
    ...  
    [last pivoted column] AS column name  
FROM  
    (SELECT query that produces the data)   
    AS alias for the source query
PIVOT  
(  
    aggregation function (column being aggregated)  
FOR   
[column that contains the values that will become column headers]   
    IN ( [first pivoted column], [second pivoted column],  
    ... [last pivoted column])  
) AS alias for the pivot table  
optional ORDER BY clause;  

So now in order to achieve the same, let write the query accordingly as per syntax.

SELECT PlayerName,UTU,Zonal  --selected non-pivoted column,pivotedColumns
FROM  
    (SELECT firstname+' '+secondname as PlayerName,tournamentName,total_Score --SELECT query that produces the data 
	from  [SampleDb].[dbo].[BatsmanStats])   
    as TblStats --alias for the source query
PIVOT  
(  
    sum(total_Score) -- aggregation function (column being aggregated)  
	for tournamentName  --[column that contains the values that will become column headers]   
	IN ( [UTU],[Zonal]) --IN ( [first pivoted column], [second pivoted column],  
   
)   
as PvtStatsPerTournament --AS alias for the pivot table 

Output