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

AOB

Registered User.
Local time
Today, 18:11
Joined
Sep 26, 2012
Messages
617
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 :

PhaseIDPhaseSortOrder
1Some Phase200
2Another Phase600
3Some Other Phase400

And the History table looks like this :

ProjectIDPhaseIDStartDateEndDate
1101 Jan 202331 Jan 2023
1201 Feb 202328 Feb 2023
1301 Mar 202331 Mar 2023
2110 Apr 202215 Apr 2022
2316 Apr 202222 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 :

Code:
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.

ProjectIDAnother PhaseSome Other PhaseSome Phase
101 Feb 202301 Mar 202301 Jan 2023
216 Apr 202210 Apr 2022

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

ProjectIDSome Phase (200)Some Other Phase (400)Another Phase (600)
101 Jan 202301 Mar 202301 Feb 2023
210 Apr 202216 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.)
 
Use an Alias, and zero or Null for the field not available in each query?
 
  • Like
Reactions: 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?
 
I tried this :

Code:
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?...

Code:
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;
 
Okay this seems to work...

Code:
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...?
 
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?
 
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!
 

Users who are viewing this thread

Back
Top Bottom