UNPIVOT in Access SQL

[Inspired]

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

 

[Banana]

It looks like it'd be something similar to this:

SELECT Type, Category, "Profit" AS ProfRevCost, Profit AS Amount
FROM theTable
UNION
SELECT Type, Category, "Revenue" AS ProfRevCost, Revenue AS Amount
FROM theTable
UNION
SELECT Type, Category, "Cost" AS ProfRevCost, Cost AS Amount
FROM theTable;

You probably also want to add a WHERE to each query so it select something like WHERE Profit IS NOT NULL or such, and maybe add GROUP BY, if that's needed.

 

[Inspired]

Thanks for this, it almost works perfectly. My only problem now is the ordering of the original column names down the rows. I'm ending up with a table containing the following column:

ProfRevCost
Cost
Profit
Revenue
Cost
Profit
Revenue
etc

It's obviously ordering it alphabetically down the rows but I would like to have Profit, Revenue, Cost as the order instead. One way to get round this is to name them 1.Profit 2.Revenue etc, but is there a way to get round this without having to use numbers or change the names?

 

[Banana]

Add one more column that will be then hidden in the final report.. call it SortKey:

SELECT Type, Category, ProfRevCost, Amount
FROM (
  SELECT Type, Category, "Profit" AS ProfRevCost, Profit AS Amount, 1 AS SortKey
  FROM theTable
  UNION
  SELECT Type, Category, "Revenue" AS ProfRevCost, Revenue AS Amount, 2 AS SortKey
  FROM theTable
  UNION
  SELECT Type, Category, "Cost" AS ProfRevCost, Cost AS Amount, 3 AS SortKey
  FROM theTable
) as u
ORDER BY Type, Category, SortKey DESC;

 

[Inspired]

Works a charm, thanks