CODEWITHSUNDEEP
sqlms-access
Laxman Rathod
Laxman Rathod

Reputation: 37

SQL Query to transpose rows to columns in MS Access database

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.

table : tssStockMaster with 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).

Required Output using SQL query in ms access database

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

Upvotes: 0

Views: 1112

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

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

Upvotes: 3

Related Questions