Access Pivot SQL Grouped by multiple columns

Question

I have the following table and want to create a new table with the data pivoted.

[report date] [Name] [Location] [Desc] [Value]
1/1/2017 Bob Spain Sales 10
1/1/2017 Bob Spain Costs 5
2/1/2017 Bob Spain Sales 20
2/1/2017 Bob Spain Costs 8
1/1/2017 Tom Spain Sales 9
1/1/2017 Tom Spain Costs 4
2/1/2017 Tom Spain Sales 19
2/1/2017 Tom Spain Costs 7

And would like to use Access VBA SQL to pivot the data as follows:

[Report Date] [Name] [Location] [Sales] [Costs]
1/1/2017 Bob Spain 10 5
2/1/2017 Bob Spain 20 8
1/1/2017 Tom Spain  9 4
2/1/2017 Tom Spain 19 7

I see a lot of examples of how to pivot with just 3 columns e.g.; [Report Date] [Desc] [Value]. Pivoting data in MS Access How can the other attribute columns be carried along?

Gustav · Answer 1 · 2019-10-13T09:35:35.480

1

You can use:

Select 
    [report date], 
    [Name],
    [Location],
    Sum(IIf([Desc] = 'Sales', [Value], 0)) As Sales,
    Sum(IIf([Desc] = 'Costs', [Value], 0)) As Costs
From
    YourTable
Group By
    [Name],
    [Location],
    [report date]

Output:

edited Oct 13 '19 at 09:35

answered Jan 20 '18 at 08:25

Gustav

53,498
7
29
55

I can not see any PIVOT expression on your suggested answer – Masoud Abrishami Oct 13 '19 at 07:15
@masoudabrishami: Not needed for only two fixed columns. See attached output, please. – Gustav Oct 13 '19 at 09:45

score 0 · Answer 2 · answered Jan 21 '18 at 05:31

Simply specify the other columns in SELECT and GROUP BY clauses of MS Access's crosstab query which contains an aggregate query nested within:

TRANSFORM MAX([Value])
SELECT [Report Date], [Name], [Location]
FROM myTable
GROUP BY [Report Date], [Name], [Location]
PIVOT [Desc];

Access Pivot SQL Grouped by multiple columns

2 Answers2