How to collase data each row in sql
i want to diaplay column in tree structure,parent node
mumbai>pune >chakan
dheli>Abc>CC>
row wise data display in column .
You must Sign In to post a response.
- Category: SQL Server
parent node
mumbai>pune >chakan
dheli>Abc>CC>
row wise data display in column . - #763140
Hi Mohan,
If you want to display row wise data in column wise (or) column wise data in row wise then use PIVOT table in SQL.
This is the sample link for the same, "technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx".
--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.
N@veen
Blog : http://naveens-dotnet.blogspot.in/ - #763296Hello 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
Return to Return to Discussion Forum
