Crosstab - column names

  • Thread starter Thread starter jliu1971
  • Start date Start date
J

jliu1971

Hi,

I have a query that looks like this:

TRANSFORM mytable.var1,Count(mytable.var2) AS cnt
SELECT mytable.var1, count(mytable.var2) AS TOTAL
FROM mytable
GROUP BY mytable.var1
PIVOT mytable.var2;

and my result looks like this:

var1 female male
1 30 10
2 40 20
3 50 30

Is there a way I can name the columns to "column1", "column2" and so
forth, instead of female and male? Assume there are any number of
columns.

Thanks in advance,
Jenni
 
My apology for the repeated posts.

Being new, I have no explanation. I first clicked send at 1:30 EST
and Google told me the server was busy. As I waited, I saw the
mistake in the TRANSFORM clause. So I posted again with the
correction at 2:50 EST. By the time I logged off Google at 4:50 EST,
my post had not shown. Before I saw them for the first time this
morning, I had assumed that it didn't go through and that I would have
to send again. What a surprise!

I'll try to do better next time.

Karl, my issue is I don't know how many levels there will be so the
iif will not work for me. Thanks anyway.

Duane, your suggestion looks great. I'll chew on it for a little
more. One question: if I construct a string of "[value1] AS [name1],
[value2] AS [name2]..." and do a "SELECT " & mystring, is it a less
efficient way?


Thanks in advance,
Jenni
 
I'm not sure what you are asking. Where would you construct this string and
how would you apply it? Are you building the SQL for a querydef?
 
Back
Top