Access SQL for multiple LEFT JOIN cant work

Question

TableA

Shop ID	Item	Price
Shop A	Item1	101
Shop A	Item2	102
Shop A	Item3	103
Shop A	Item4	104
Shop A	Item5	105
Shop A	Item6	106
Shop A	Item7	107
......	.....	.....
Shop A	Item27	127
Shop B	Item1	201
Shop B	.....	.....
Shop B	Item27	227
Shop C	Item1	301
Shop C	.....	.....
Shop C	Item27	327

Suppose I have a table like above in which I would like to convert to table below

Shop ID	Item1	Item2	Item3	Item4	.....	Item27
Shop A	101	102	103	104	.....	127

I tried using query with multiple left joins

SELECT T1.[Shop ID], T1.[Price] AS Item1 .... T27.[Price] AS Item 27

FROM (TableA AS T1
LEFT JOIN TableA AS T2 ON T1.[Shop ID] = T2.[Shop ID])
LEFT JOIN TableA AS T3 ON T1.[Shop ID] = T3.[Shop ID])
...
LEFT JOIN TableA AS T27 ON T1.[Shop ID] = T27.[Shop ID]

WHERE T1.[Item] = 'Item1'
AND T2.[Item] = 'Item2'
...
AND T27.[Item] = 'Item27'
AND T1.[Shop ID] = 'Shop A'

This work for smaller number of LEFT JOIN but when number of LEFT JOIN > 20, Access essentially stopped forever, my actual table is a bit more complicate. Any one can suggest ways for the conversion? TIA

Answer 1

One approach is to use a pivot query:

SELECT
    [Shop ID],
    MAX(IIF([Item] = "Item1", Price, NULL)) AS Item1,
    MAX(IIF([Item] = "Item2", Price, NULL)) AS Item2,
    MAX(IIF([Item] = "Item3", Price, NULL)) AS Item3,
    ...
    MAX(IIF([Item] = "Item27", Price, NULL)) AS Item27
FROM TableA
GROUP BY [Shop ID]
ORDER BY [Shop ID];

You may also look into cross tab options with MS Access.