Category: SQL Server

03 Nov 2015 10:25

Joined:	26/10/2013
Level:	Bronze
Points:	71

Points:	1

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 .

#763140

06 Nov 2015 04:29

Joined:	03/05/2013
Level:	Gold
Points:	15349

Points:	2

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/

#763296

25 Nov 2015 06:12

Joined:	29/06/2010
Level:	Silver
Points:	230

Points:	4

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

How to collase data each row in sql

Email subscription