473,699 Members | 2,248 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Converting Columns into Rows without affecting the data in the table

Vasuki Masilamani
18 New Member
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
6 9626
willakawill
1,646 Top Contributor
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
18 New Member
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
168 Recognized Expert New Member
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
18 New Member
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
168 Recognized Expert New Member
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
18 New Member
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

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

Similar topics

7
23499
by: Pooj | last post by:
have a urgent requirement. Please somebody help me. I have a table departinfo with following records begin_time end_time Name Pieces 10:00 10:15 PopCorn 3 10:15 10:30 Biscuits 5 10:30 10:45 PopCorn 2
3
2545
by: Diego TERCERO | last post by:
Hi... I'm working on a tool for editing text resources for a family of software product my company produces. These text resources are found in a SQL Server database, in a table called "Resource" with the following structure : Resource{,en,fr,es} Yes.. these are the only languages supported actually. A couple of rows in that table would look like this :
36
4469
by: kjvt | last post by:
Based on a prior posting, I've written a function to convert a recordset to a dataview. The first call to the function for a given recordset works perfectly, but the second call always returns a dataview with a count = 0. Can someone explain why and how I might work around this problem? Here is the code for my function: Public Shared Function GetViewFromRS(ByVal pRS As ADODB.Recordset) _ As DataView
1
2640
by: Ramakrishnan Nagarajan | last post by:
Hi, I am converting Excel data into a Dataset in C#. There are around 24 columns in the Excel Sheet. First I tried to insert one row with correct values in the Excel sheet. i.e. for text columns I entered text values and for numeric columns I entered numeric values. It works fine and pass through all the validation checks and gets inserted into the database successfully. But when I gave some junk values in the excel sheet and tried to...
13
4648
by: ppateel | last post by:
Hi, I am new to c++ and I am converting a c program to c++. I changed malloc call to new and I am getting an exception violation. Here is the relevant piece of code. Compiler vc++ 7.0 (.Net 2003) SQLINTEGER nCols; SQLINTEGER cbColDataLength; PBYTE* pColumnData = NULL;
2
5456
by: Greg | last post by:
I have a gridview on my form which I have populated using a datareader. What I would like to do is to be able to remove specified records from the gridview, without affecting the source of the data. I'm a bit confused about how to go about removing the rows - all the google searches I have done so far seem to assume that the grid view is bound and the programmer wants to delete the data from the original source. Is there any easy way to...
2
3506
by: Nacho | last post by:
Hi, I'm designing a new database and I have a doubt in which surely you can help me. I'm storing in this database historical data of some measurements and the system in constantly growing, new measurements are added every day. So, I have to set some extra columns in advance, so space is available whenever is needed and the client doesn't have to modify the structure in SQL server.
2
1940
by: truva | last post by:
Hi, I need a query which would convert Rows into Columns 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: LOGTYPE CLLI SWREL RPTDATE CNAME CVALUE avl-stplan tahlt01 EAGLE537 08/11/2007 TCPRSTSENT 12 avl-stplan tahlt01 EAGLE537 08/11/2007 IPHDRERR 88 avl-stplan tahlt01 EAGLE537 08/11/2007 IPADDRERR 65...
0
2371
by: Orbie | last post by:
Hi Guys, I need some help with pivoting or converting some rows on a Table into columns using SQL Server 2008! I have a Table which contains the same Products in 4 different Stores. I'm only interested in the Product_ID, Description and the Estimated Price fields for Store 1, however for the other stores (2,3 and 4) i need to display the Actaul Price and the Size on the same line of output. I've attached an example of the Table which...
0
8689
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8618
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9178
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9035
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
7752
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4376
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4631
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3058
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2010
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.