TableA

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

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

CodePudding user response：

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.

CodePudding user response：

Try CrossTab query like-

TRANSFORM First(t.Price) AS FirstOfPrice
SELECT t.ShopID
FROM TableA as t
GROUP BY t.ShopID
ORDER BY t.Item
PIVOT t.Item;

Input:

Output: