By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
463,190 Members | 563 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 463,190 IT Pros & Developers. It's quick & easy.

Converting Columns into Rows without affecting the data in the table

Vasuki Masilamani
P: 18
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.
Dec 7 '06 #1
Share this Question
Share on Google+
6 Replies

100+
P: 1,646
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.
Dec 7 '06 #2

Vasuki Masilamani
P: 18
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.
Dec 7 '06 #3

almaz
Expert 100+
P: 168
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:
Expand|Select|Wrap|Line Numbers
  1. declare @months table(ID int identity (1,1))
  2.  
  3. declare @i int set @i = 0
  4. while(@i<3)
  5. begin
  6.     insert @months default values
  7.     set @i = @i + 1
  8. end
  9.  
  10. select [Month] = M.ID, Emp.Name, Emp.EmployeeID, Emp.[Year], 
  11.     Expense = case 
  12.         when M.ID = 1 then JanExp
  13.         when M.ID = 2 then FebExp
  14.         when M.ID = 3 then MarExp
  15.     end,
  16.     Hours = case 
  17.         when M.ID = 1 then JanHrs
  18.         when M.ID = 2 then FebHrs
  19.         when M.ID = 3 then MarHrs
  20.     end
  21. from Employees
Dec 7 '06 #4

Vasuki Masilamani
P: 18
This sample should give you a hint:
Expand|Select|Wrap|Line Numbers
  1. declare @months table(ID int identity (1,1))
  2.  
  3. declare @i int set @i = 0
  4. while(@i<3)
  5. begin
  6.     insert @months default values
  7.     set @i = @i + 1
  8. end
  9.  
  10. select [Month] = M.ID, Emp.Name, Emp.EmployeeID, Emp.[Year], 
  11.     Expense = case 
  12.         when M.ID = 1 then JanExp
  13.         when M.ID = 2 then FebExp
  14.         when M.ID = 3 then MarExp
  15.     end,
  16.     Hours = case 
  17.         when M.ID = 1 then JanHrs
  18.         when M.ID = 2 then FebHrs
  19.         when M.ID = 3 then MarHrs
  20.     end
  21. 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.
Dec 7 '06 #5

almaz
Expert 100+
P: 168
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:
Expand|Select|Wrap|Line Numbers
  1. create table #Employees
  2.     (
  3.      EmployeeID int,
  4.      Name nvarchar(100),
  5.      [Year] int,
  6.      JanExp float,
  7.      FebExp float,
  8.      MarExp float,
  9.      JanHrs float,
  10.      FebHrs float,
  11.      MarHrs float
  12.     )
  13. insert #Employees (Name, EmployeeID, [Year], JanExp, FebExp, MarExp, JanHrs, FebHrs, MarHrs) 
  14.     values ('A', 129178, 2005, 10, 20, 30, 15, 25, 35)
  15. insert #Employees (Name, EmployeeID, [Year], JanExp, FebExp, MarExp, JanHrs, FebHrs, MarHrs) 
  16.     values ('B', 129213, 2004, 40, 50, 60, 45, 55, 65)
  17. insert #Employees (Name, EmployeeID, [Year], JanExp, FebExp, MarExp, JanHrs, FebHrs, MarHrs) 
  18.     values ('C', 129038, 2005, 70, 80, 90, 75, 85, 95)
  19.  
  20. declare @months table
  21.     (
  22.      ID int identity(1, 1)
  23.     )
  24.  
  25. declare @i int
  26. set @i = 0
  27. while(@i < 3)
  28.     begin
  29.         insert  @months
  30.                 default values
  31.         set @i = @i + 1
  32.     end
  33.  
  34. select [Month] = M.ID, Emp.Name, Emp.EmployeeID, Emp.[Year], 
  35.     Expense = case when M.ID = 1 then JanExp
  36.          when M.ID = 2 then FebExp
  37.          when M.ID = 3 then MarExp
  38.     end,
  39.     Hours = case when M.ID = 1 then JanHrs
  40.                  when M.ID = 2 then FebHrs
  41.                  when M.ID = 3 then MarHrs
  42.             end
  43. from #Employees Emp cross join @months M
Dec 7 '06 #6

Vasuki Masilamani
P: 18
Ooops, sorry, I forgot to add the most important part :).
Here is complete sample:
Expand|Select|Wrap|Line Numbers
  1. create table #Employees
  2.     (
  3.      EmployeeID int,
  4.      Name nvarchar(100),
  5.      [Year] int,
  6.      JanExp float,
  7.      FebExp float,
  8.      MarExp float,
  9.      JanHrs float,
  10.      FebHrs float,
  11.      MarHrs float
  12.     )
  13. insert #Employees (Name, EmployeeID, [Year], JanExp, FebExp, MarExp, JanHrs, FebHrs, MarHrs) 
  14.     values ('A', 129178, 2005, 10, 20, 30, 15, 25, 35)
  15. insert #Employees (Name, EmployeeID, [Year], JanExp, FebExp, MarExp, JanHrs, FebHrs, MarHrs) 
  16.     values ('B', 129213, 2004, 40, 50, 60, 45, 55, 65)
  17. insert #Employees (Name, EmployeeID, [Year], JanExp, FebExp, MarExp, JanHrs, FebHrs, MarHrs) 
  18.     values ('C', 129038, 2005, 70, 80, 90, 75, 85, 95)
  19.  
  20. declare @months table
  21.     (
  22.      ID int identity(1, 1)
  23.     )
  24.  
  25. declare @i int
  26. set @i = 0
  27. while(@i < 3)
  28.     begin
  29.         insert  @months
  30.                 default values
  31.         set @i = @i + 1
  32.     end
  33.  
  34. select [Month] = M.ID, Emp.Name, Emp.EmployeeID, Emp.[Year], 
  35.     Expense = case when M.ID = 1 then JanExp
  36.          when M.ID = 2 then FebExp
  37.          when M.ID = 3 then MarExp
  38.     end,
  39.     Hours = case when M.ID = 1 then JanHrs
  40.                  when M.ID = 2 then FebHrs
  41.                  when M.ID = 3 then MarHrs
  42.             end
  43. from #Employees Emp cross join @months M

Thank You So much. I got the solution.
Dec 7 '06 #7

Post your reply

Sign in to post your reply or Sign up for a free account.