How to Transform rows into column in SQL Server.

Problem: How to Transform rows into column in SQL Server.

Solution:

PIVOT, SQL Server 2005 or Later.

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output

Example 1:

Let’s do a basic example of rotating the values and showing particular aspects of data. For, this example I chose employees group by employee type and then I would flip those rows to columns.

SELECT count(EmployeeID) as Employeecount,[EmployeeType]

           FROM  [dbo].[Employees]

             group by    [EmployeeType]

SELECT *

FROM

 (

    SELECT EmployeeID,[EmployeeType]

           FROM  [dbo].[Employees]

) as s

PIVOT

(

    COUNT(EmployeeID)

    FOR [EmployeeType] IN

(

[Architect],[Developer],[General employee],[Solution Consultant],[Tester])

)AS pvt

Output:

Example 2:

Generally, people prefer using pivots when they have dates and aggregations so I would like to show you one more example, where I have History of call entries from two years. Year and months slice and dice

SELECT count(callid) as CallCount,year( [Entry_date]) as Entry_year, DATENAME(month, [Entry_date]) as Entry_month

           FROM  [dbo].[history]

  group by year( [Entry_date]),DATENAME(month, [Entry_date])

SELECT *

FROM

 (

    SELECT count(callid) as CallCount,year( [Entry_date]) as Entry_year,DATENAME(month, [Entry_date]) as Entry_month

           FROM  [dbo].[history]

         group by year( [Entry_date]),DATENAME(month, [Entry_date])
)

) as s

PIVOT

(

    SUM(CallCount)

    FOR [Entry_month] IN

(

January,February,March,April,May,June,July,August,September,October,November,Decemeber

)

)AS pvt

Output: