transform table from horizontal to vertical

[enfinity]

Hi there, I got a little data transformation problem:

I have got a source table:

ID 1 2 3
1 x y z
2 xx yy zz

That I wanna transform to the following format:

ID ID2 Comment
1 1 x
1 2 y
1 3 z
2 1 xx
etc...


The problem is, that I need a routine that takes the column names of table 1 and uses them as values in table 2. So basically, every column of table 1 (1,2,3, etc.) should become a new data record in table 2.

Any ideas how to solve this?

Thanks!
Steve

 

[Moniker]

You're pretty much describing a Crosstab query. Search on those if you're lost as to how they work. Also, to see this in action, select a single row and copy it, open Excel, do a Paste Special, and select the "Transpose" checkbox at the bottom of the Paste Special menu. That's pretty much was a Crosstab Query does (as well as a Pivot Table in Excel).