Converting Columns into Rows without affecting the data in the table

**willakawill** · Dec 7 '06, 08:26 AM

Originally posted by 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.

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

**Vasuki Masilamani** · Dec 7 '06, 08:52 AM

Originally posted by willakawill

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** · Dec 7 '06, 08:58 AM

Originally posted by Vasuki Masilamani

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:

Code:

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** · Dec 7 '06, 10:41 AM

Originally posted by almaz

This sample should give you a hint:

Code:

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** · Dec 7 '06, 11:09 AM

Originally posted by Vasuki Masilamani

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:

Code:

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** · Dec 7 '06, 11:50 AM

Originally posted by almaz

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

Code:

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.

Converting Columns into Rows without affecting the data in the table

Converting Columns into Rows without affecting the data in the table

Comment

Comment

Comment

Comment

Comment

Comment