SQL Query to transpose rows to columns in MS Access database

Question

I am using MS Access DB it has a table named ‘tssStockMaster’ with fields (PartNo, ItemName,Stock,Workshop) as shown in the image with following data.

Now I am unable to write a query in MS Access Database using SQL to get the following output such that the stock quantity is shown for each workshops against each item name(group by PartNo).

Since we cant use PIVOT in MS Access how can I achieve this ?

Can you use TRANSFORM? https://stackoverflow.com/questions/16546305/pivoting-data-in-ms-access — James Cooke, Jun 26 '18 at 09:31

score 3 · Accepted Answer · answered Jun 26 '18 at 09:33

If you know up front how many workshops will be there and the number isn't significant I bet you could use aggregate function with if conditions:

select 
    partno
  , itemname
  , sum(iif(workshop = 'W101', stock, 0)) as w101
  , sum(iif(workshop = 'Z239', stock, 0)) as z239
from t
group by partno, itemname

SQL Query to transpose rows to columns in MS Access database

1 Answers1