Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

UNPIVOT in Access SQL?

Status
Not open for further replies.
Marco123

Marco123

Programmer
Dec 31, 2010
23
GB
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
 
Sort by date Sort by votes
I expect you could use a union query:
Code: 
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

lameid
  • Locked
  • Question
SELECT FROM Table in Another Access File
Replies
2
Views
319
lameid
lameid
Swi
  • Locked
  • Question
Jet Sql Statement - Select Distinct Multiple Columns 1
Replies
4
Views
259
Swi
Swi
monkeysee
  • Locked
  • Question
Automatic Invoicing in Access 1
Replies
6
Views
163
monkeysee
monkeysee
JK Dutta
  • Locked
  • Question
requet for form an access query for report
Replies
2
Views
314
dhookom
dhookom
combo
  • Locked
  • Question
'Wrong property setting' message in query 1
Replies
2
Views
323
combo
combo

Part and Inventory Search

Sponsor

Back
Top