How to reorder data from rows to columns?

[steve riley]

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

[colintis]

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.

INSERT INTO Drum2(DRUM, CABLETAG) SELECT DRUM,CableTag01 FROM Drum1

INSERT INTO Drum2(DRUM, CABLETAG) SELECT DRUM,CableTag02 FROM Drum1

[steve riley]

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

[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 :

SELECT [DRUM]
     , [CableTag01] AS CABLETAG
FROM   [tblCableTag]
UNION ALL
SELECT [DRUM]
     , [CableTag02]
FROM   [tblCableTag]
WHERE  ([CableTag02] Is Not Null)
UNION ALL
SELECT [DRUM]
     , [CableTag03]
FROM   [tblCableTag]
WHERE  ([CableTag03] Is Not Null)
UNION ALL
...