473,326 Members | 2,076 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.

How to reorder data from rows to columns?

i have a query that gives me this info
DRUM CableTag01 CableTag02
Drum1 C-001 C-101
Drum2 P-544 P-454
Drum3 C-545
Drum4 C-444 C-777

but i want it diplayed like this

DRUM CABLETAG
DRUM1 C-001
DRUM1 C-101
DRUM2 P-544
DRUM2 P-454
DRUM3 C-545
DRUM4 C-444
DRUM4 C-777

The info is imported automatically from a program so i need to write a query or something to sort the data the way i need
any ideas
thanks
Nov 5 '10 #1

✓ answered by NeoPa

So, the problem is that your question doesn't accurately reflect what you need? We prefer members to consider their questions before posting to avoid such wastage of other members' time.

Regardless of how many columns you have, the approach shouldn't change fundamentally. I would suggest using a UNION ALL query, but I'm still not aware of the data very reliably. I assume that CableTag01 is a field which is reliably populated but all the other CableTag fields are optional.

Here is a basic format of the SQL, but you may need to do some work to handle your situation correctly :
Expand|Select|Wrap|Line Numbers
  1. SELECT [DRUM]
  2.      , [CableTag01] AS CABLETAG
  3. FROM   [tblCableTag]
  4. UNION ALL
  5. SELECT [DRUM]
  6.      , [CableTag02]
  7. FROM   [tblCableTag]
  8. WHERE  ([CableTag02] Is Not Null)
  9. UNION ALL
  10. SELECT [DRUM]
  11.      , [CableTag03]
  12. FROM   [tblCableTag]
  13. WHERE  ([CableTag03] Is Not Null)
  14. UNION ALL
  15. ...

3 1948
colintis
255 100+
It would be best to create another table (e.g. Drum2) with your desired formatting. Then use INSERT to move the data with 2 queries.
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Drum2(DRUM, CABLETAG) SELECT DRUM,CableTag01 FROM Drum1
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Drum2(DRUM, CABLETAG) SELECT DRUM,CableTag02 FROM Drum1
Nov 5 '10 #2
thanks for your quick reply colintis
there is one problem though
cabletag01 , 02 etc can go to about 100 columns
and drum1, 2 etc can equally go for about 100 rows
Nov 5 '10 #3
NeoPa
32,556 Expert Mod 16PB
So, the problem is that your question doesn't accurately reflect what you need? We prefer members to consider their questions before posting to avoid such wastage of other members' time.

Regardless of how many columns you have, the approach shouldn't change fundamentally. I would suggest using a UNION ALL query, but I'm still not aware of the data very reliably. I assume that CableTag01 is a field which is reliably populated but all the other CableTag fields are optional.

Here is a basic format of the SQL, but you may need to do some work to handle your situation correctly :
Expand|Select|Wrap|Line Numbers
  1. SELECT [DRUM]
  2.      , [CableTag01] AS CABLETAG
  3. FROM   [tblCableTag]
  4. UNION ALL
  5. SELECT [DRUM]
  6.      , [CableTag02]
  7. FROM   [tblCableTag]
  8. WHERE  ([CableTag02] Is Not Null)
  9. UNION ALL
  10. SELECT [DRUM]
  11.      , [CableTag03]
  12. FROM   [tblCableTag]
  13. WHERE  ([CableTag03] Is Not Null)
  14. UNION ALL
  15. ...
Nov 5 '10 #4

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

Similar topics

6
by: Jennifer Smith | last post by:
I want to be able to display my recordset as follows: a e b f c g d h Instead of : a b c d
0
by: Vamsi Polavarapu | last post by:
Hi, I have two Data Tables,now i want a collection of only those rows which are different in either one.Can any one suggest me a way to compare two Data Rows. Both the Data Tables are identical...
1
by: authorking | last post by:
How could I add data rows into a datagrid controll so that I can edit my data in the data rows.
0
by: earsypaul | last post by:
Hello, With MS SQL server we can bcp hexadecimal data into varbinary columns quite nicely. We're migrating some data to DB2 and trying to import to a "varchar for bit data" data type column....
2
by: J055 | last post by:
Hi I've been looking into different ways of formatting columns/rows in the GridView control. I realize now, I think, that once the datasource is bound to the control the original column type...
2
by: frankkirchner | last post by:
I'm new to both VS2005 and C# Web Forms (Though I have tons of experience with VB.Net and VS2003). Creating a Data Source visually in VS2005 is easy as pie - I love it, but how do you or would...
0
by: yoav.sagi | last post by:
hi, i am Inheriting from TableLayoutPane with a designer that inherits from ParentControlDesigner ..i would like to allow the user to resize the columns and rows ..All rows and columns SizeType =...
6
by: viki1967 | last post by:
Hi all. I need your help. I realize this script and I do not know where to begin: 1) A simple grid rows / columns. 2) The first column contain an input type = "checkbox" 3) When select...
3
by: ronakinuk | last post by:
how can i unhide rows/columns in excel 2007
1
by: nawedita | last post by:
Hi everyone how i add and delete rows,and columns(textbox,dropdown) dynamically onclick button and store data in database with insert,update,delete record.that support all browsers.Because i am...
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: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
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...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
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
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.