CODEWITHSUNDEEP
sqlms-accessms-access-2016
Ravi
Ravi

Reputation: 55

MS Access SQL convert Rows to Columns

I need some help with converting an MS Access table with data in Rows into Columns. Created some sample data table that suits my scenario. I need to convert "Table 1" to "Table 3". If possible using the "Table 2" to identify the column order of the Fruits. enter image description here

I think this can be done using Pivot option in SQL Server. How can I achieve the same in Access Database?

Thanks.

Upvotes: 2

Views: 3279

Answers (2)

Parfait
Parfait

Reputation: 107642

Consider MS Access' crosstab query which uses TRANSFORM and PIVOT clauses. The IN list in PIVOT specifies the order and appearance (omit or not) of columns.

TRANSFORM MAX(t1.Export) AS MaxExport
SELECT t1.Country
FROM table1 t1
GROUP BY t1.Country
PIVOT t1.Fruit IN ('Apple', 'Kiwi', 'Mango', 'Grapes', 'Pear')

For needed order with number period column headers, simply join the second table and concatenate Order and Fruit columns in PIVOT clause:

TRANSFORM MAX(t1.Export) AS MaxExport
SELECT t1.Country
FROM table1 t1
INNER JOIN table2 t2
   t1.Fruit = t2.Fruit
GROUP BY t1.Country
PIVOT t2.[Order] & "." & t1.Fruit

Upvotes: 1

June7
June7

Reputation: 21370

Column Order is dictated by value in the column header. Could concatenate Fruit and Order fields:
Order & "-" & Fruit AS OrderFruit.

Use query designer to build query that joins Table1 and Table2 on Fruit fields with that calculated field and switch to CROSSTAB to complete.

Upvotes: 1

Related Questions