473,326 Members | 2,081 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Multi-Column Transpose in Union Query

doma23
107 100+
Hi, my problem is next:

I have existing table like this:

01/01/2010 02/01/2010 03/01/2010
code1 val11 val12 val13
code2 val21 val22 val23
code3 val31 val32 val33

And I need to reorganize it and somehow transform it to new table like this:

DATE VALUE
code1 01/01/2010 val11
code1 02/01/2010 val12
code1 03/01/2010 val13
code2 01/01/2010 val21
code2 02/01/2010 val22
code2 03/01/2010 val23
code3 01/01/2010 val31
code3 02/01/2010 val32
code3 03/01/2010 val33

I have no idea how to begin with this?
Thank you!

SOLUTION:
Actually, I've solved it with union query:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblOld.Desc, "01/01/2010" AS [Date], tblOld.[01/01/2010] AS [Value]
  2. FROM tblOld
  3. union all
  4. SELECT tblOld.Desc, "02/01/2010" AS [Date], tblOld.[02/01/2010] AS [Value]
  5. FROM tblOld
  6. UNION ALL SELECT tblOld.Desc, "03/01/2010" AS [Date], tblOld.[03/01/2010] AS [Value]
  7. FROM tblOld;
  8.  
But the problem is that there is a ton of these dates arreanged in columns. Is there a way to automatize it or I need to write SQL code manually for each date?
Sep 23 '10 #1
1 2434
Try to use Cross-Tab Query
Dec 21 '10 #2

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

Similar topics

3
by: Dan Williams | last post by:
I'm trying to do a simple alteration to the table design of one of our SQL 2k tables, simply changing an identity row so that its not 'not for replication', and its taking absolutely ages to do so,...
1
by: mksql | last post by:
As an example, I am building an authentication mechanisim that will use data in the 3 left tables to determine rights to objects in a destination table, diagrammed below. In this structure,...
2
by: deko | last post by:
This may be an easy question, but for some reason the multiple table design idea is throwing me. I'm trying to avoid using one large, wide table - so I've got multiple tables that hold different...
8
by: Stewart Allen | last post by:
Hi Just asking for ideas on table design. The design I have is as follows: *tblBuildData* BuildID (PK) AutoNumber ManufactureDate SerialNumber
5
by: BerkshireGuy | last post by:
Hello everyone, I want to create an employee license plate database and need help with the best table design. I was thinking three tables: 1) tblEmployees EmployeeID EmployeeName
6
by: MLH | last post by:
If I open an A97 table, resort its key-field to descending order and attempt to close the table, A97 asks me if I wish to save the table DESIGN? Now really, I don't think the table design is being...
4
by: lorirobn | last post by:
Hello, I'd be curious to hear other thoughts on my database and table design. My database is for 'space use' in a lodging facility - will hold all spaces, like rooms (lodging rooms, dining...
3
by: shahram.shirazi | last post by:
Hi guys, I was wondering if someone could help me a bit here. Im trying to desing an electronic register system for a school. In terms of the table design, I obviously need a Student Details...
1
by: keliie | last post by:
I have a relatively simple (I assume) issue which I am at a complete loss to address. My issues is: I want to populate fields in my tables with summary data from the same table. Let me explain: ...
0
by: jsimone | last post by:
This question is about DB2 table design and performance. We are using DB2 UDB Enterprise 8.2 on Linux. We have 6 tables in a parent-child (one-to-many) relationship with each other. Each...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.