You must Sign In to post a response.
  • Category: SQL Server

    How to collase data each row in sql

    i want to diaplay column in tree structure,

    parent node
    mumbai>pune >chakan

    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, "".

    Give respect to your work, Instead of trying to impress your boss.

    Blog :

  • #763296
    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)
    --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)

    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
          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 :-


Sign In to post your comments