CODEWITHSUNDEEP
sqlms-access
Makis
Makis

Reputation: 337

Transposing rows to columns in MsAccess query

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!

Upvotes: 0

Views: 1379

Answers (1)

Fionnuala
Fionnuala

Reputation: 91326

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;

Upvotes: 2

Related Questions