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

    To convert column data into row..in sql

    am using sql server 2008.I need column to convert row in dynamicly...in sql server....

    Name ---- date ----- attendance

    aaa ---- 2014/02/02 ---- P
    bbb ---- 2014/02/03 ---- A
    . . .
    . . .
    ---------------------------------------------------------------------
    to Change

    Name ---- 2014/02/02 ---- 2014/02/03 --- 2014/20/04 ............ Avg
    aaa ---- p ---- a ---- p ---- 50%
    bbb ---- A ---- P ---- P ----

    -----------------------------------------------------------------------
    How to do very ?Urgent.........................
  • #749470
    Hai kirubaharan,
    To Convert the column data in to row and vice versa, you can use CTE(Common Table Expressions) which is the concept came with SQL Server 2005.
    You can follow the below link for the sample queries and accordingly you can generate your query:

    http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #749532
    Hi We Can do with the Pivot method in sql for converting ROWS to Column

    http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server
    this above link shows that using pivot

    http://www.aspdotnet-suresh.com/2012/09/how-to-convert-rows-to-columns-in-sql.html

    In the above method simple Sql Query without Pivot method hope it will be useful.

    Rajesh B
    To Live More,Learn More

  • #749604
    PIVOT is the option which you can use


    -- Pivot table with one row and five columns
    SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
    [0], [1], [2], [3], [4]
    FROM
    (SELECT DaysToManufacture, StandardCost
    FROM Production.Product) AS SourceTable
    PIVOT
    (
    AVG(StandardCost)
    FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
    ) AS PivotTable;


    Thanks & Regards
    Anil Kumar Pandey
    Microsoft MVP, DNS MVM

  • #749632
    Hi,

    Refer the below SQL Script

    CREATE TABLE table1
    (
    name VARCHAR(10),
    [date] DATE,
    attendance VARCHAR(10)
    )

    INSERT INTO table1
    VALUES ('aaa',
    '2014/02/02',
    ' P'),
    ('bbb',
    '2014/02/02',
    ' P'),
    ('aaa',
    '2014/02/03',
    ' P'),
    ('bbb',
    '2014/02/03',
    ' A')

    DECLARE @ColumnName AS VARCHAR(MAX),
    @SQLQuery AS VARCHAR(MAX)

    SELECT @ColumnName = COALESCE(@ColumnName + ', ', '') + '['
    + LTRIM(RTRIM(a.[date])) + ']'
    FROM (SELECT DISTINCT [date] AS [Date]
    FROM table1) a

    SET @SQLQuery = 'SELECT Name, ' + @ColumnName
    +
    ' FROM (SELECT Name, [Date],attendance FROM Table1) AS SourceTable PIVOT ( MAX(attendance) FOR [Date] IN ('
    + @ColumnName + ') ) AS pivottable';

    EXEC(@SQLQuery)

    DROP TABLE table1

    Regards
    Siva

  • #749711
    You can use CTE query for this and you can use PIVOT also for retrieving columns as rows and vise versa.
    Try to search regarding PIVOT/UNPIVOT in sql server,
    You got thousands of results and you will get idea about PIVOT/UNPIVOT table.
    You will get solution for sure by your self.

    Regards,
    Nirav Prabtani (Senior Web Developer)
    Email : niravjprabtani@gmail.com
    blog : niravprabtani.blogspot.in

  • #749768
    Hi,

    If you want to convert row into column and column into row then go with PIVOT TAble to achieve this.

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

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #749902
    Hi,

    To convert row data into column data, use PIVOT TABLE command of SQL.
    First insert all the values in your table and then do like below-

    SELECT *
    FROM (
    SELECT
    left(datename(month,dt),3)as [month],
    Val
    FROM #tblname
    ) as s
    PIVOT
    (
    SUM(Val)
    FOR [month] IN (jan, feb, mar, apr,
    may, jun, jul, aug, sep, oct, nov, dec)
    )AS pivott

    Thanks,
    Ashutosh Jha
    http://tricksroad.com


  • Sign In to post your comments