How to change column values to rows in Access db

Question

I have a table generated by a query in Access as something like this--

Id	length	Height	Diameter	code
1-X(C1)	1	4	100	S W
1-Y(C2)	3	7	200	N

which I want to change to --

Id	Feature	Value
1-X(C1)	length	1
1-X(C1)	Height	4
1-X(C1)	Diameter	100
1-X(C1)	code	S W
1-Y(C2)	length	3
1-Y(C2)	Height	7
1-Y(C2)	Diameter	200
1-Y(C2)	code	N

I have tried using UNPIVOT function but it doesn't work, I want the feature to be in a particular order(length, height, diameter, code) I am using Access query design, is there a way to do this?

Answer 1

You have to UNION 4 queries. Something like:

select id, "length" as Feature, ("" & length) as value from myTable
union all
select id, "height" as Feature, height as value from myTable
union all
select id, "diameter" as Feature, diameter as value from myTable

Not sure value is not a reserved word ?

---

Since [value] is going to contain a mix of text and number, I guess you will have to convert it all to text. I did it for the first row, just to show.

Answer 2

I've not fully tested, but you could have a secondary table containing the fields, in the order you expect.

So in this case i had piv and piv_order in a table called pivs. And length as order 1, height as 2 and so on. Probably not the most ideal solution as would need to maintain this table, or the most efficient.

SELECT distinct t1.id, p.piv as Feature, 
DLookUp(p.piv,"table1","id=" & t1.id) AS FeatureValue 
FROM table1 AS t1, pivs AS p
ORDER BY t1.id, p.piv_order;