Converting Columns into Rows without affecting the data in the table

[Vasuki Masilamani]

Hi,
I need a query which would convert Columns into Rows without causing any damages to the original data. I am not supposed to solve this by creating temporary tables and later dropping it.

I have a table which is in the below format:

Name EmployeeID Year JanExp FebExp MarExp JanHrs FebHrs MarHrs

A 129178 2005 10 20 30 15 25 35
B 129213 2004 40 50 60 45 55 65
C 129038 2005 70 80 90 75 85 95


I need a Output which has the following format:

Month Name EmployeeID Year Expenses Hours
Jan A 129178 2005 10 15
Jan B 129213 2005 40 45
Jan C 129038 2004 70 75
Feb A 129178 2005 20 25
Feb B 129213 2005 50 55
Feb C 129038 2004 80 85
Mar A 129178 2005 30 35
Mar B 129213 2005 60 65
Mar C 129038 2004 90 95

Please help with some solution.

Thanks,
Vasuki.

[willakawill]

Hi,
I need a query which would convert Columns into Rows without causing any damages to the original data. I am not supposed to solve this by creating temporary tables and later dropping it.

I have a table which is in the below format:

Name EmployeeID Year JanExp FebExp MarExp JanHrs FebHrs MarHrs

A 129178 2005 10 20 30 15 25 35
B 129213 2004 40 50 60 45 55 65
C 129038 2005 70 80 90 75 85 95


I need a Output which has the following format:

Month Name EmployeeID Year Expenses Hours
Jan A 129178 2005 10 15
Jan B 129213 2005 40 45
Jan C 129038 2004 70 75
Feb A 129178 2005 20 25
Feb B 129213 2005 50 55
Feb C 129038 2004 80 85
Mar A 129178 2005 30 35
Mar B 129213 2005 60 65
Mar C 129038 2004 90 95

Please help with some solution.

Thanks,
Vasuki.

Hi
if you create a new table and insert the data from the old table. It wil be fine.

[Vasuki Masilamani]

Hi
if you create a new table and insert the data from the old table. It wil be fine.

Hi,
I got a solution by creating a table and inserting data into it. But my requirement is to do without creating any tables.

Help me with a solution.

Thanks,
Vasuki.

[almaz]

Hi,
I need a query which would convert Columns into Rows without causing any damages to the original data...

This sample should give you a hint:

declare @months table(ID int identity (1,1))
 
declare @i int set @i = 0
while(@i<3)
begin
    insert @months default values
    set @i = @i + 1
end
 
select [Month] = M.ID, Emp.Name, Emp.EmployeeID, Emp.[Year], 
    Expense = case 
        when M.ID = 1 then JanExp
        when M.ID = 2 then FebExp
        when M.ID = 3 then MarExp
    end,
    Hours = case 
        when M.ID = 1 then JanHrs
        when M.ID = 2 then FebHrs
        when M.ID = 3 then MarHrs
    end
from Employees

[Vasuki Masilamani]

This sample should give you a hint:

declare @months table(ID int identity (1,1))
 
declare @i int set @i = 0
while(@i<3)
begin
    insert @months default values
    set @i = @i + 1
end
 
select [Month] = M.ID, Emp.Name, Emp.EmployeeID, Emp.[Year], 
    Expense = case 
        when M.ID = 1 then JanExp
        when M.ID = 2 then FebExp
        when M.ID = 3 then MarExp
    end,
    Hours = case 
        when M.ID = 1 then JanHrs
        when M.ID = 2 then FebHrs
        when M.ID = 3 then MarHrs
    end
from Employees

I can understand the logic of this code. But I am facing some syntax errors in this. Can you please help me in this. Any apt solution would be helpful for me.

Thanks,
Vasuki.

[almaz]

I can understand the logic of this code. But I am facing some syntax errors in this. Can you please help me in this. Any apt solution would be helpful for me.

Thanks,
Vasuki.

Ooops, sorry, I forgot to add the most important part :).
Here is complete sample:

create table #Employees
    (
     EmployeeID int,
     Name nvarchar(100),
     [Year] int,
     JanExp float,
     FebExp float,
     MarExp float,
     JanHrs float,
     FebHrs float,
     MarHrs float
    )
insert #Employees (Name, EmployeeID, [Year], JanExp, FebExp, MarExp, JanHrs, FebHrs, MarHrs) 
    values ('A', 129178, 2005, 10, 20, 30, 15, 25, 35)
insert #Employees (Name, EmployeeID, [Year], JanExp, FebExp, MarExp, JanHrs, FebHrs, MarHrs) 
    values ('B', 129213, 2004, 40, 50, 60, 45, 55, 65)
insert #Employees (Name, EmployeeID, [Year], JanExp, FebExp, MarExp, JanHrs, FebHrs, MarHrs) 
    values ('C', 129038, 2005, 70, 80, 90, 75, 85, 95)
 
declare @months table
    (
     ID int identity(1, 1)
    )
 
declare @i int
set @i = 0
while(@i < 3)
    begin
        insert  @months
                default values
        set @i = @i + 1
    end
 
select [Month] = M.ID, Emp.Name, Emp.EmployeeID, Emp.[Year], 
    Expense = case when M.ID = 1 then JanExp
         when M.ID = 2 then FebExp
         when M.ID = 3 then MarExp
    end,
    Hours = case when M.ID = 1 then JanHrs
                 when M.ID = 2 then FebHrs
                 when M.ID = 3 then MarHrs
            end
from #Employees Emp cross join @months M

[Vasuki Masilamani]

Ooops, sorry, I forgot to add the most important part :).
Here is complete sample:

create table #Employees
    (
     EmployeeID int,
     Name nvarchar(100),
     [Year] int,
     JanExp float,
     FebExp float,
     MarExp float,
     JanHrs float,
     FebHrs float,
     MarHrs float
    )
insert #Employees (Name, EmployeeID, [Year], JanExp, FebExp, MarExp, JanHrs, FebHrs, MarHrs) 
    values ('A', 129178, 2005, 10, 20, 30, 15, 25, 35)
insert #Employees (Name, EmployeeID, [Year], JanExp, FebExp, MarExp, JanHrs, FebHrs, MarHrs) 
    values ('B', 129213, 2004, 40, 50, 60, 45, 55, 65)
insert #Employees (Name, EmployeeID, [Year], JanExp, FebExp, MarExp, JanHrs, FebHrs, MarHrs) 
    values ('C', 129038, 2005, 70, 80, 90, 75, 85, 95)
 
declare @months table
    (
     ID int identity(1, 1)
    )
 
declare @i int
set @i = 0
while(@i < 3)
    begin
        insert  @months
                default values
        set @i = @i + 1
    end
 
select [Month] = M.ID, Emp.Name, Emp.EmployeeID, Emp.[Year], 
    Expense = case when M.ID = 1 then JanExp
         when M.ID = 2 then FebExp
         when M.ID = 3 then MarExp
    end,
    Hours = case when M.ID = 1 then JanHrs
                 when M.ID = 2 then FebHrs
                 when M.ID = 3 then MarHrs
            end
from #Employees Emp cross join @months M

Thank You So much. I got the solution.