sorting columns in crosstab query (xxx@0, xxx@1, xxx@2, ... , xxx@N)

[Lars Schouw]

Hi,

I want to sort the columns from my pivot crosstab query but don't know
how to do it!

The RAW data looks like this:

id groupname keyname value
A product xxx@1 1
B product xxx@33 2
C product xxx@10 3
D product xxx@53 4
E product xxx@59 5

The sql select statement

TRANSFORM First(value) AS FirstMyValue
SELECT id
FROM Bkascproplist
WHERE keyname like 'xxx@*'
GROUP BY id
PIVOT keyname;

returns

id xxx@0 xxx@1 xxx@10 xxx@99 xxx@2
A 2 4 3 1 5

But I would like not to have the columns sorted so that the result
looks like this
id xxx@0 xxx@1 xxx@2 xxx@10 xxx@99
A 2 4 5 3 1

Can anyone please help me here?

Lars

 

[Lars Schouw]

I found one solution that seem to work: adding in ("xxx@0",
"xxx@1", .... ) to the sql statement.
Is there any easier solution that this one?

 

[Dale Fye]

No, the only way to get the column headers to sort the way you want is to add
the pivot clause. Otherwise, the columns will sort alphabetically from left
to right.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[John Spencer]

Another way would be to change the values in Keyname to include leading zeroes
xxx@01 instead of xxx@1
xxx@00 instead of xxx@0
xxx@02 instead of xxx@2

Or if the values in keyname are consistent - Always start with xxx@, you could
use.

TRANSFORM First(value) AS FirstMyValue
SELECT id
FROM Bkascproplist
WHERE keyname like 'xxx@*'
GROUP BY id
PIVOT Left(keyname,4) & Format(Val(Mid(KeyName,5)),"00")

If keyname is always a string of characters followed by an "@" followed by a
number sequence you might need to use Instr to locate the "@"

PIVOT Left(KeyName,Instr(1,KeyName,"@") &
Format(Val(Mid(KeyName,Instr(1,Keyname,"@")+1)),"00")

If some of the numbers are in hundreds or thousands change the format string
from "00" to "000" or "0000".

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County