Hello Mohan,
You can use PIVOT table for displaying row wise data in
column wise.
In other words, PIVOT is one of the New relational operator introduced in Sql Server 2005. It provides an easy mechanism in Sql Server to transform rows into columns.
To understand PIVOT with extensive list of examples, let us first create a Temporary Table #CourseSales with sample records as depicted in the below image by using the following script:
Temp Table #CourseSales
Course Year Earning
.NET 2013 15000.00
PHP 2014 13500.00
PHP 2014 10500.00
.NET 2013 5000.00
.NET 2014 15000.00
PHP 2013 7500.00
--Create Temporary Table #CourseSales
CREATE TABLE #CourseSales
(Course VARCHAR(50),Year INT,Earning MONEY)
GO
--Populate Sample records
INSERT INTO #CourseSales VALUES('.NET',2012,10000)
INSERT INTO #CourseSales VALUES('Java',2012,20000)
INSERT INTO #CourseSales VALUES('.NET',2012,5000)
INSERT INTO #CourseSales VALUES('.NET',2013,48000)
INSERT INTO #CourseSales VALUES('Java',2013,30000)
GO
Example 1:
In this example the #CourseSales Table data is Pivoted so that the Course becomes the column headings.
Below script pivots the #CourseSales Table data so that the Course columns distinct values are transformed as Columns.
SELECT *
FROM #CourseSales
PIVOT(SUM(Earning)
FOR Course IN ([.NET], Java, PHP)) AS tbl
the output for the above query as below :-
Course .NET PHP
2013 20000.00 7500.00
2014 15000.00 24000.00
Please also refer to the following sites :-
http://sqlhints.com/2014/03/10/pivot-and-unpivot-in-sql-server/
http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query
Thanks