Solved Sort transformed columns non-alphabetically with TRANSFORM / PIVOT query (1 Viewer)

[AOB]

I have two tables with a one-to-many relationship which I want to be able to query in order to "flatten" the data for reporting purposes.

I have a table of "phases" and a table of "phase histories" which records the start and end dates for each parent project for any of the phases through which it passes.

So the Phase table looks like this :

PhaseID	Phase	SortOrder
1	Some Phase	200
2	Another Phase	600
3	Some Other Phase	400

And the History table looks like this :

ProjectID	PhaseID	StartDate	EndDate
1	1	01 Jan 2023	31 Jan 2023
1	2	01 Feb 2023	28 Feb 2023
1	3	01 Mar 2023	31 Mar 2023
2	1	10 Apr 2022	15 Apr 2022
2	3	16 Apr 2022	22 Apr 2022

What I'm trying to produce is a report that transposes all of the phases into columns with the start and end dates for each corresponding project.

I can achieve this for the start and end dates independently with the following :

TRANSFORM First(tblHistory.StartDate) AS FirstOfStartDate
SELECT tblHistory.ProjectID
FROM tblHistory
INNER JOIN tblPhases ON tblHistory.PhaseID = tblPhases.PhaseID
GROUP BY tblHistory.ProjectID
PIVOT tblPhases.Phase;

(and can use a similar query for the end dates)

The problem is, the transposed Phases (columns) are ordered alphabetically left-to-right.

ProjectID	Another Phase	Some Other Phase	Some Phase
1	01 Feb 2023	01 Mar 2023	01 Jan 2023
2		16 Apr 2022	10 Apr 2022

I need to sort the transposed Phase columns using the SortOrder column in the Phase table.

ProjectID	Some Phase (200)	Some Other Phase (400)	Another Phase (600)
1	01 Jan 2023	01 Mar 2023	01 Feb 2023
2	10 Apr 2022	16 Apr 2022

Is this possible?

Also - given that I will need two separate queries for the start and end dates - is there any way I can dynamically rename the columns (i.e. instead of "Some Phase", make it "Some Phase - Start Date") so that I can distinguish between the two when I try to join the two results together?

Bearing in mind - the phases themselves may change over time so I don't want to hard-code specific names into the SQL. I just want to apply a generic logic to whatever phases are in the list at any given time (some may be added, others may be removed / renamed etc.)

 

[Gasman]

Use an Alias, and zero or Null for the field not available in each query?

 

[AOB]

Thanks @Gasman but you've lost me there? How can I alias a pivot field and how would that get around the sorting issue?

 

[AOB]

I tried this :

TRANSFORM First(tblHistory.StartDate) AS FirstOfStartDate
SELECT tblHistory.ProjectID
FROM tblHistory
INNER JOIN tblPhases ON tblHistory.PhaseID = tblPhases.PhaseID
GROUP BY tblHistory.ProjectID
PIVOT tblPhases.SortOrder & " - " & tblPhases.Phase;

But that doesn't work properly because "1000 - Arbitrary Phase" comes before "200 - Some Other Arbitrary Phase"?...

This works perfectly in terms of ordering - but I lose the names of the individual phases?...

TRANSFORM First(tblHistory.StartDate) AS FirstOfStartDate
SELECT tblHistory.ProjectID
FROM tblHistory
INNER JOIN tblPhases ON tblHistory.PhaseID = tblPhases.PhaseID
GROUP BY tblHistory.ProjectID
PIVOT tblPhases.SortOrder;

 

[AOB]

Okay this seems to work...

TRANSFORM First(tblHistory.StartDate) AS FirstOfStartDate
SELECT tblHistory.ProjectID
FROM tblHistory
INNER JOIN tblPhases ON tblHistory.PhaseID = tblPhases.PhaseID
GROUP BY tblHistory.ProjectID
PIVOT Format(tblPhases.SortOrder,"0000") & " - " & tblPhases.Phase;

Only problem now is knowing how large the largest SortOrder value is such that there are sufficient leading zeroes to accommodate it...?

 

[Gasman]

Thanks @Gasman but you've lost me there? How can I alias a pivot field and how would that get around the sorting issue?

Well I was thinking get the order sorted before the pivot. If your sort field is text with numbers, that is always going to happen?

 

[AOB]

If your sort field is text with numbers, that is always going to happen?

It's not, my sort field - [SortOrder] in the Phases table - is a numerical field?

And ordering prior to the pivot makes no difference; the pivot is still going to apply those transposed columns alphabetically as it sees them.

But my previous post looks like it will do the trick - a tad messy but I can script some cleanup once I've gotten the transposed data into Excel!

Thanks again for the pointer, I wouldn't have considered the alias if you hadn't suggested it!