Rotating a table using Pivot Operator in SQL

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:

1

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

2

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

5

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.