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 am using Access query design, is there a way to do this?
CodePudding user response：
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.
CodePudding user response：
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;