UNPIVOT in Access SQL?

[Marco123]

Hi,

Is it possible to use the UNPIVOT function in Access SQL? I have a table with 5 columns (Type, Category, Profit, Revenue, Cost) as shown below:

Type Category Profit Revenue Cost
A Small £150 £200 £50
A Medium etc
A Large
B Small
B Medium
B Large
C Small
Etc

What I want to be able to do is create a new table with the layout:

Type Category ProfRevCost Amount
A Small Profit £150
A Small Revenue £200
A Small Cost £50
A Medium
Etc

Any ideas how to do this in SQL within Access?

Thanks

 

[dhookom]

I expect you could use a union query:

SELECT Type, Category, "Profit" as ProfRevCost, Profit as Amount
FROM tblWith5Columns
UNION ALL
SELECT Type, Category, "Revenue", Revenue
FROM tblWith5Columns
UNION ALL
SELECT Type, Category, "Cost", Cost
FROM tblWith5Columns

Duane
Hook'D on Access
MS Access MVP

 

[Marco123]

Thanks, does just the job.