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

  • Thread starter Thread starter Lars Schouw
  • Start date Start date
L

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
 
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?
 
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.
 
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
 
Back
Top