2006.02.22 06:33 PM

Access SQL TRANSFORM Statement, Alternate PIVOT Ordering

A reader named Patrick left a comment on my earlier Access SQL TRANSFORM Statement post asking whether it is possible to sort a crosstab's column values using a column other than the one specified in the PIVOT clause:

Great Post! Just found it. Is there a way to specify the sort of your series? I have an Access chart report and its record source is the following:

TRANSFORM Avg(qryCWT_Last_3_Months.doc_pcnt) AS AvgOfdoc_pcnt
SELECT (Format([d_iss_month],"mmm"" '""yy")) AS Expr1
FROM qryCWT_Last_3_Months
GROUP BY (Year([d_iss_month])*12+Month([d_iss_month])-1), (Format([d_iss_month],"mmm"" '""yy"))
PIVOT qryCWT_Last_3_Months.sof;

The SOF is my horizontal series. The table has a Primary Key named SOF_ID. I don’t need or want the PK to show up on the chart but I would like the series to sort by PK rather than alphabetically. Any ideas?

I pointed out in my response that it is possible to sort a crosstab's column values using a regular ORDER BY, however, the column given in the ORDER BY must appear in one of the query's aggregate functions or groupings (GROUP BY or unconstrained PIVOT), otherwise you get an error 3122.

There are a couple of ways around this, though. One way is to write and call some code to pre-select the unique set of values on which you want to sort, along with their associated values, which you want to appear in the columns, and use the latter to construct a SQL TRANSFORM having a constrained and explicitly ordered PIVOT clause (i.e., a PIVOT with an IN statement). This can even be done in a generic fashion, but it's a pain in the ass to write and use.

Alternatively, you can game the system with just a little bit of code and get the same thing for less. This simpler approach takes advantage of the fact that JET's evaluation of whether or not an ORDER BY column or expression appears in an aggregate function or grouping is based on expression signatures, not on what the expressions actually do. Unlike SQL Server, which uses various rules to ensure determinism (particularly as it relates to indexes on computed columns), JET simply compares the signature of the expressions and assumes that the same signature returns the same value. However, even having made that assumption, JET still treats query expressions as volatile, which means it calculates query expressions each time they are needed - it does not cache expression results.

Taken together, these things mean that we can write a function having a single signature that can vary what it returns when it is called and then use this function in a TRANSFORM statement's ORDER BY clause to get one value for sorting and in its PIVOT clause to get another value for the columns. All we need is a little state in the form of a local static variable. Here's one way to write that function:

Function AorB(ByVal A As Variant, ByVal B As Variant) As Variant

  Static LastA As Variant
  
  If LastA <> A Then
    AorB = A
    LastA = A
  Else
    AorB = B
    LastA = Empty
  End If

End Function

And here's Patrick's crosstab query modified to use this function to produce columns showing SOF sorted by SOF_ID:

TRANSFORM Avg(qryCWT_Last_3_Months.doc_pcnt) AS AvgOfdoc_pcnt
SELECT (Format([d_iss_month],"mmm"" '""yy")) AS Expr1
FROM qryCWT_Last_3_Months
GROUP BY (Year([d_iss_month])*12+Month([d_iss_month])-1), (Format([d_iss_month],"mmm"" '""yy"))
ORDER BY AorB(qryCWT_Last_3_Months.sof_id, qryCWT_Last_3_Months.sof)
PIVOT AorB(qryCWT_Last_3_Months.sof_id, qryCWT_Last_3_Months.sof);

The function AorB will be called twice for each source row. The first time in the context of the ORDER BY, so it returns the first value (SOF_ID), and the second time in the context of the PIVOT grouping, so it returns the second value (SOF).

I should point out that I've never actually used this technique in a production system, and am not sure whether it works in versions of Access prior to 2003 (though I think it should). So, if you decide to use it, you should test very thoroughly and be very careful. Good luck.


Comments

Not bad!

A while ago, I came across a *neat* site run by a guy called "Peat" which shows you how to do crosstabs in pure "vanilla" SQL.

Here's the link -

http://www.advogato.org/person/peat/diary.html?start=15

( If you're around, Peat please forgive me for posting your example, but it's just "soooo good"! )

Here it is -

SELECT lakename,
COUNT(CASE WHEN year = 1996 THEN depth ELSE NULL END) AS SY1996,
COUNT(CASE WHEN year = 1997 THEN depth ELSE NULL END) AS SY1997,
COUNT(CASE WHEN year = 1998 THEN depth ELSE NULL END) AS SY1998

FROM crosstab
GROUP BY lakename

"Lakename" is the row-variable and SY1996, SY1997, SY1998 are the column variables.
Very good stuff!

- Andy

;

Andy Elvey | 2006.02.23 10:57 AM

Cool articles but I couldn't get the AorB() working.
Any suggestions?

tblCols:
id month
0 dummy
1 jan
2 feb
3 mar
4 apr
5 may
6 jun

tblData:
name month val
Joe feb 2
Joe jan 1
Joe mar 3
Joe apr 4
Bob feb 12
Bob jan 11
Bob mar 13
Bob apr 14

qryCross2 results:
name 1 apr feb jan mar
Bob 11 14 12 13
Joe 4 2 1 3

qryCrossAorB:
TRANSFORM First(qryData.val) AS val
SELECT qryData.name
FROM qryData
GROUP BY qryData.name
ORDER BY AorB(qryData.id,qryData.month)
PIVOT AorB(qryData.id,qryData.month);

qryData:
SELECT tblData.name, tblData.month, tblCols.id, tblData.val
FROM tblData INNER JOIN tblCols ON tblData.month = tblCols.month
ORDER BY tblData.name, tblData.month;

DEBUG:
A: 1 B:jan AorB: 1 LastA: 1
A: 1 B:jan AorB:jan LastA:
A: 2 B:feb AorB: 2 LastA: 2
A: 2 B:feb AorB:feb LastA:
A: 2 B:feb AorB: 2 LastA: 2
A: 2 B:feb AorB:feb LastA:
A: 3 B:mar AorB: 3 LastA: 3
A: 3 B:mar AorB:mar LastA:
A: 3 B:mar AorB: 3 LastA: 3
A: 3 B:mar AorB:mar LastA:
A: 4 B:apr AorB: 4 LastA: 4
A: 4 B:apr AorB:apr LastA:
A: 4 B:apr AorB: 4 LastA: 4
A: 4 B:apr AorB:apr LastA:

Rich | 2006.05.17 11:38 PM

Hi Rich,

Thanks for the heads-up. After much trial and error, I've discovered a couple of things about this that I probably should have discovered when I first wrote it up.

The first thing is that the evaluation order of the ORDER BY and PIVOT expressions can vary. Who knew? It certainly didn't vary in my earlier tests of Patrick's query. Worse, it varies in different ways depending on whether the corsstab query's data source is a saved query (as in your example), or an actual table (I created one based on the results of your qryData query), or a sub-SELECT within the crosstab query.

The second thing is that the evaluation of the expressions in the ORDER BY and PIVOT clauses are sometimes repeated for a single row, resulting in an odd number of calls to AorB.

Needless to say, both of these things are bad for a routine like AorB, which uses a simple toggle and expects a determinate one-at-a-time evaluation of source rows.

After fooling around some, I found that the indeterminate evaluation of rows can be resolved using something more advanced than a binary toggle in AorB, like a key collection; however, the indeterminate evaluation of the ORDER BY and PIVOT, and the occassional repeated evaluation of their expressions for the same rows, cannot be resolved.

Bummer. Live by the hack, die by the hack, I guess.

Given you're working with a limited and known set of potential column headings, the only thing I can suggest for your query is to explicitly specify the column headings in the appropriate order:

TRANSFORM First(qryData.val) AS val
SELECT qryData.name
FROM qryData
GROUP BY qryData.name
PIVOT qryData.month in ("jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec")

Thanks again for pointing this out. Good luck.

ewbi.develops | 2006.05.18 02:57 PM

Very interesting. Could you help on this : I do crosstabs in access from a "facts table" using transform and i would like to keep the result of the transform query INTO a new empty table. Any clue ?
Thanks

G.G.Casanova | 2007.02.05 01:56 PM

G. G. Casanova,

I assume you've long since stopped waiting for a reply. Sorry I wasn't able to get to you sooner.

If the target table does not already exist, you can use your crosstab query as the source for a SELECT...INTO query:

http://msdn2.microsoft.com/en-us/library/bb208934.aspx

If the target table does already exist, you can use your crosstab query to insert rows into it using an INSERT INTO query:

http://msdn2.microsoft.com/en-us/library/bb208861.aspx

However, in both cases you will need to save and name your crosstab query first, as Access does not allow direct use of TRANSFORM queries as sub-queries in SELECT...INTO or INSERT INTO queries.

ewbi.develops | 2007.04.26 01:03 PM

Hello All! Very nice JOB!!!
RESPECT

vasya_dwh | 2008.05.27 10:40 AM

Hi!

I have an ASP code similar given by ewbi.develops.

sql ="TRANSFORM First(qryData.val) AS val SELECT qryData.name FROM qryData GROUP BY qryData.name
PIVOT qryData.month in ("jan", "feb", "mar", "apr", may", "jun", "jul", "aug", "sep", "oct", "nov", "dec");"

set oRs = Server.CreateObject("ADODB.Recordset")
oRs.open sql, oConn, 1, 2

my question is: how can I retrieve jan/feb/mar/... values???

oRS("????");


thanks a lot!!

And I want to know how can I retrieve values (jan, feb, mar, etc...) from query. How is the sintaxis?

Assuming

Daniel | 2009.06.14 03:42 PM

Hi Daniel,

The pivoted values, in this case jan, feb, etc., become the names of the columns or fields of the recordset. So, you can access them as:

Debug.Print oRS.Fields("jan").Value
Debug.Print oRS.Fields("feb").Value
etc.

Or, in light of the Fields collection being the default property of Recordset, and Value being the default property of Field, we can just use this shorthand:

Debug.Print oRS("jan")

Good luck!

ewbi.develops | 2009.06.14 08:27 PM

Thanks for your reply, ewbi.develops. You are right.

Daniel | 2009.06.15 01:28 AM

Hi,
Do you know how can I save this data in a table. I know that it is not possible to use INTO.

Carlos | 2010.05.12 12:33 PM

Hi Carlos,

I believe you have to first save your TRANSFORM query. Once saved with a unique name, you can use it as the data source for an INSERT...INTO query just like you would use a table. Good luck!

ewbi.develops | 2010.05.12 04:09 PM

I have a table in ACCESS ....
id no txt
50251 2 a
50251 1 c
50251 2 d
50251 2 f
50251 2 b
50251 3 e
50251 1 g
50251 4 h

I am looking to transform this in to

ID 1 1 2 2 2 2 3 4
50251 c g a d f b e h

Droximz | 2011.08.10 02:11 AM

That's fairly easy to achieve, but it requires the pivot values be unique for each combination of no and txt:

TRANSFORM first(txt)
SELECT id
FROM droximz
GROUP BY id
PIVOT [no] & txt

This unfortunately results in column headers like this:

id 1c 1g 2a 2b 2d 2f 3e 4h
50251 c g a b d f e h

Which kind of defeats the purpose of having the TRANSFORM values repeated in the body. I suppose you could make those look better with a dash or something. Or maybe save the TRANSFORM SQL as a query and the use a separate query to override the resulting column names (of course this presumes you'd know all of the possible column values in advance).

Good luck!

ewbi.develops | 2011.08.10 11:15 AM

Thanks for the PIVOT [NO]&txt

I am busy converting an Access system to C# .net web base, and I was able to use that then strip off the number as the gridview is rendered.

Warren | 2011.10.22 10:50 PM

The AorB function works beautifully!

I wanted to sort by an integer field and display the corressponding Text field.

It was fantastic.

Chris | 2012.07.18 12:47 PM


TrackBack

TrackBack URL:  http://www.typepad.com/services/trackback/6a00d8341c7bd453ef00d83478992053ef

Listed below are links to weblogs that reference Access SQL TRANSFORM Statement, Alternate PIVOT Ordering: