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

    How to convert columns into rows in sql server

    i have emp table as follows

    Empid Empname Month Salary

    1 A 1 3000
    2 B 2 5000
    3 C 3 8000
    4 D 4 9000
    5 E 5 7000


    from the above i want the output as follows


    Empid Empname 1 2 3 4 5

    1 A 3000 5000 8000 9000 7000


    from getting a above output how to write the query in sql server
  • #769089
    Hi,

    You can use PIVOT Table concept in SQL server that will help you to give Rows to columns...

    Refer below link this might be helpful to you..
    https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

    Hope this helps you...

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

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

  • #769090
    SELECT EMPNAME,[1]AS JAN,[2] AS FEB
    FROM
    (SELECT EMPNAME,MONTHID,SALARY FROM EMPLOYEE)P
    PIVOT
    (
    SUM(SALARY)FOR MONTHID IN([1],[2])
    )AS PVT

    This query is showing the sample upto Feb month . you have to extend upto the dec month.I have checked the correctness of the query. It is working fine as per your expectation.

  • #769118
    You can use PIVOT Table concept in SQL server to convert columns into rows in sql server. Here is an example fo code You need to change code according to your source
     

    declare @T table (ScripName varchar(50), ScripCode varchar(50), Price int)
    insert into @T values ('1 A 1; 3000)

    select
    'ScripName' as ColName,
    ScripName as ColValue
    from @T
    union all
    select
    'ScripCode' as ColName,
    ScripCode as ColValue
    from @T
    union all
    select
    'Price' as ColName,
    cast(Price as varchar(50)) as ColValue
    from @T


Sign In to post your comments