Format table with SQL to put multiple entry on one row

Question

I'm currently working on an Access database, and I would like to make a query that can fuse multiple rows into one, but keeping all the entries separated in differents columns.

The table i'm making my query on is called "Monthly", is used for monthly reports of activity and is made this way:

project	date	turnover	margin
0001	01/01/2021	10000	20%
0001	01/02/2021	10500	15%
0002	01/01/2021	8500	25%

I would like to know if there is a way to transform it into this:

project	date	turnover	margin	date	turnover	margin
0001	01/01/2021	10000	20%	01/02/2021	10500	15%
0002	01/01/2021	8500	25%	01/02/2021

I first thought of using GROUP BY, but it's clearly not adapted.

The objective would be to format it on a SQL request, not to modify the structure/ how the data is stored.

Thanks in advance for anyone who answers.

Create a spreadsheet with the desired 12 sets of columns, then loop the table and fill in the cells as you go. — Gustav, Dec 23 '21 at 10:37
What should happen when you have three (or more) records for 1 project ? — Luuk, Dec 23 '21 at 12:03
what you try to do is PIVOTing, see: [Pivoting data in MS Access](https://stackoverflow.com/questions/16546305/pivoting-data-in-ms-access) and/or [Create PivotTable or PivotChart views in an Access desktop database](https://support.microsoft.com/en-us/office/create-pivottable-or-pivotchart-views-in-an-access-desktop-database-83e524df-dfbd-456d-9dd0-0a48c1aa6752) — Luuk, Dec 23 '21 at 12:06

June7 · Accepted Answer · 2021-12-23T11:57:57.253

If it doesn't already exist, add an autonumber field to table.

Consider:

Query1:

SELECT ID, project, [date] AS Data, "D" AS Cat FROM ProjectData
UNION SELECT ID, project, turnover, "T" FROM ProjectData
UNION SELECT ID, project, margin, "M" FROM ProjectData;

Query2:

TRANSFORM First(Query1.Data) AS FirstOfData
SELECT Query1.project
FROM Query1
GROUP BY Query1.project
PIVOT DCount("*","ProjectData","Project='" & [Project] & "' AND ID<" & [ID])+1 & [Cat];

Presume this will be a multi-year db so probably need some filter criteria to limit selection to one year.

Similar example Pivot Query in MS Access

For more about CROSSTAB queries, review http://allenbrowne.com/ser-67.html

Or emulate CROSSTAB (example for 2 months):

SELECT ProjectData.project, 
Max(IIf(Month([date])=1,[Date],Null)) AS Jan, Max(IIf(Month([date])=1,[Margin],Null)) AS JanMargin, Max(IIf(Month([date])=1,[turnover],Null)) AS JanTurnover, 
Max(IIf(Month([date])=2,[Date],Null)) AS Feb, Max(IIf(Month([date])=2,[Margin],Null)) AS FebMargin, Max(IIf(Month([date])=2,[turnover],Null)) AS FebTurnover
FROM ProjectData
GROUP BY ProjectData.project;

Should not use reserved words as names. Date is a reserved word.

Format table with SQL to put multiple entry on one row

1 Answers1