Transposing rows to columns in MsAccess query

Question

I have a table with

cID, side, row, column

with some data of

24, 1, 10, 5
25, 1, 12, 6
24, 2, 18, 3

and so on. Now I want these data to be show in the form of:

cID=24
side    1   2
row    10  18
column  5   3

cID=25
side     2
row     12
column   6

The cID is filtered in the query so the output will be the 3 rows (side, row, column) and the data of them of a specific cID. Is that possible with MsAccess Query/SQL and how?

Thanks!

Fionnuala · Accepted Answer · 2012-11-24 20:54:53Z

2

Something on these lines:

TRANSFORM First(q.rvalue) AS firstofrow
SELECT q.rhead
FROM   (SELECT cid,
               side,
               row   AS rvalue,
               "row" AS rhead
        FROM   atable
        UNION ALL
        SELECT cid,
               side,
               column   AS rvalue,
               "column" AS rhead
        FROM   atable) AS q
WHERE   q.cid = 24
GROUP  BY q.rhead
PIVOT q.side;

answered Nov 24, 2012 at 20:54

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Makis Over a year ago

Thank you for your reply Remou. Your solution is close to what I want, but the rhead is a head of the results. I want it also a row data. And for head we can use a numbering 1,2,3... or the autonumbering ID which provided by table definition (I didn't include this field in the question, but there is).

Fionnuala Over a year ago

Paste the data you did provide into a table called atable. You will find that both row and column are returned. You can use the idea to work with the data you did not provide.

Collectives™ on Stack Overflow

Transposing rows to columns in MsAccess query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related