Pivoting in sql

                 Pivoting in sql means changing rows to columns and columns to rows. We often need to change rows to columns in some scenario. Suppose I have a table say "Users" having columns Id,Name and UserType. Now if there is requirement to find number of users under each UserType I can use GROUP BY clause. But if I want UserType as Column and under them count of users in each UserType I can simply pivot the table we obtain from GROUP BY clause.

Example

1) Here is my Users table.

SELECT * FROM Users

2) To find number of user in each UserType we can use GROUP BY clause.

SELECT UserType,Count(*) As 'Number of Users' FROM Users GROUP BY UserType

3) Now pivoting table.

SELECT 'Number of Users' As UserType,[1],[2],[3] FROM (SELECT UserType FROM Users) AS SourceTable
PIVOT  
(  
Count(SourceTable.UserType)  
FOR UserType IN ([1],[2],[3])  
) AS PivotTable; 

If I only want value of UserType as column and count of user then I can use the following query.

SELECT [1],[2],[3] FROM (SELECT UserType FROM Users) AS SourceTable
PIVOT  
(  
Count(SourceTable.UserType)  
FOR UserType IN ([1],[2],[3])  
) AS PivotTable;